Функция выбор в excel

Пример 3: Покажем сумму продаж для выбранного магазина

Вы можете использовать функцию CHOOSE (ВЫБОР) в сочетании с другими функциями, например, SUM (СУММ). В этом примере мы получим итоги продаж по определённому магазину, задав его номер в функции CHOOSE (ВЫБОР) в качестве аргумента, а также перечислив диапазоны данных по каждому магазину для подсчёта итогов.

В нашем примере номер магазина (101, 102 или 103) введён в ячейке C2. Чтобы получить значение индекса, такое как 1, 2 или 3, вместо 101, 102 или 103, используйте формулу: =C2-100.

Данные о продажах для каждого магазина находятся в отдельном столбце, как показано ниже.

Внутри функции SUM (СУММ) в первую очередь будет выполнена функция CHOOSE (ВЫБОР), которая вернет требуемый диапазон для суммирования, соответствующий выбранному магазину.

Это пример ситуации, когда гораздо эффективнее использовать другие функции, такие как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ). Далее в нашем марафоне мы увидим, как они работают.

Функция ИНДЕКС() в MS EXCEL

Синтаксис функции

​ Однако наша цель​​ функция «ПОИСКПОЗ» возвращает​

​ не будут работать​​ из 4 столбцов​ выше, т.к. функция​

​А6:А9.​​Функция ИНДЕКС(), английский вариант​ выгода хранить всё​ пересечении нужных строки​В этой версии функции​ аргументы позволяют указать​ и ссылку на​

​ ленты меню, найти​​ 7.​ B10 должен быть​, убедитесь, что формула​3​ — автоматизировать этот​ только одно значение​

​ с большими массивами​ и 4 строк​ возвращает не само​Выведем 3 первых​ INDEX(), возвращает значение​ в таком виде​ и столбца.​ ИНДЕКС можно установить​

​ номера интересующих строки​ эту ячейку. Поэтому​ секцию с инструментами​В ячейку B14 введите​ в порядке убывания​ работает неправильно данное.​«уд»​ процесс. Для этого​ (самое первое, т.​ данных. Дело в​​ (см. таблицу). Если​​ значение, а ссылку​

Значение из заданной строки диапазона

​ значения из этого​​ из диапазона ячеек​

​ и на одном​Спасибо Вам за​ другую функцию. Для​ и столбца относительно​ можно использовать запись​

​ «Работа с данными»​ следующую формулу:​ формулы для работы.​​Если функция​​14​ следует вместо двойки​ е. верхнее). Но​ том, что использование​

Значение из заданной строки и столбца таблицы

​ ввести в одну​ (адрес ячейки) на​​ диапазона, т.е. на​

​ по номеру строки​ листе?​ помощь.​ примера я сделал​ выбранного диапазона, а​ типа E2:ИНДЕКС(…). В​ и выбрать инструмент​

Использование функции в формулах массива

​В результате получаем значение​ Но значения в​ПОИСКПОЗ​10​ и единицы, которые​ что делать, если​ функции «ИНДЕКС» в​ из ячеек таблицы​ значение. Вышеуказанная формула​А6А7А8​ и столбца. Например,​ЗЫ Столбец B​Буду изучать.​ три таблицы с​

​ также порядковый номер​​ результате выполнения функция​​ «Проверка данных»:​ на пересечении столбца​ порядке возрастания, и,​​не находит искомое​​14​ указывают на искомые​​ в списке есть​​ Excel предполагает ввод​ расположенное вне диапазона​ =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)​​. Для этого выделите​​ формула =ИНДЕКС(A9:A12;2) вернет​

​ — лишний​VDM​ ценами из разных​​ диапазона (если диапазоны​​ ИНДЕКС вернет ссылку​В открывшемся диалоговом окне​ 3 и строки​

​ которая приводит к​ значение в массиве​12​ строку и столбец,​​ повторения. В таком​​ номера строки и​ А1:Е5 выражение «=ИНДЕКС​Аналогичный результат можно получить​​ 3 ячейки (​​ значение из ячейки​Logist​

Использование массива констант

​: Попробовал с «интерсект»​ магазинов. Диапазонам данных​ ячеек не являются​

​ на ячейку, и​ необходимо выбрать «Тип​

ПОИСКПОЗ() + ИНДЕКС()

​ 7:​ ошибке # н/д.​ подстановки, возвращается ошибка​«отлично»​ в массиве записать​ случае помогают формулы​ столбца не самой​ (В2:Е5, 2, 3)»​

​ используя функцию СМЕЩ() ​А21 А22А23​А10​: В оригинале у​Учитывает вариант, если​​ я дал именам,​

​ смежными, например, при​ приведенная выше запись​ данных:» — «Список»​Как видно значение 40​Решение:​ # н/д.​6​ соответствующие функции «ПОИСКПОЗ»,​ массива. Для их​ таблицы, а массива​ (без кавычек) и​=СУММ(СМЕЩ(A2;;;4))​), в Строку формул​, т.е. из ячейки​ файла каждый лист​

Ссылочная форма

​ даты будут повторяться.​ соответствующие их названиям,​ поиске в различных​ примет, например, следующий​

​ и указать в​ имеет координаты Отдел​​Измените аргумента​​Если вы считаете, что​3​ выдающие эти номера.​ использования следует выделить​ данных. Это достаточно​ нажать на «Ввод»,​Теперь более сложный пример,​

​ введите формулу =ИНДЕКС(A6:A9;0),​

​ расположенной во второй​ это месяц так​Sub test()​ используя поле «Имя».​ таблицах). В простейшем​ вид: E2:E4 (если​ поле «Источник» диапазон​ №3 и Статья​

​тип_сопоставления​ данные не содержится​4​ Обратите внимание, что​ весь диапазон данных​ затруднительно сделать, когда​ то в ответ​ с областями.​ затем нажмите​

​ строке диапазона.​ что дальше дробить​

​Dim Col As​

​ Теперь диапазоны называются​ случае функция ИНДЕКС​

​ выбрана команда «Манчестер​ ячеек:​ №7. При этом​

​1 или сортировка​ данных в электронной​«хорошо»​ мы ищем выражение​ и использовать сочетание​

​ речь идет о​ будет выдано значение​Пусть имеется таблица продаж​CTRL+SHIFT+ENTER​​ИНДЕКС​​ по листам не​ Integer, Dt As​ Магазин1(B2:E5), Магазин2(B8:E11) и​ возвращает значение, хранящееся​ Ю.».​Переходим в ячейку A13​ функцией ИНДЕКС не​ по убыванию формат​ таблице, но не​

excel2.ru>

Решение уравнений

Кроме того, хотя это и не является профильной возможностью данной функции, её можно использовать для решения уравнений. Правда, инструмент подбора параметра можно с успехом использовать только относительно уравнений с одним неизвестным.

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».

Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».

В открывшемся окне подбора параметра, в поле «Установить в ячейке» указываем адрес, по которому мы записали уравнение (C2). В поле «Значение» вписываем число 45, так как мы помним, что уравнение выглядит следующим образом: 15x+18x=46. В поле «Изменяя значения ячейки» мы указываем адрес, куда будет выводиться значение x, то есть, собственно, решение уравнения (B2). После того, как мы ввели эти данные, жмем на кнопку «OK».

Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.

Изучив инструмент Подбор параметра, мы выяснили, что это довольно простая, но вместе с тем полезная и удобная функция для поиска неизвестного числа. Её можно использовать как для табличных вычислений, так и для решения уравнений с одним неизвестным. Вместе с тем, по функционалу она уступает более мощному инструменту Поиск решения.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Microsoft Excel: выпадающие списки

Создание дополнительного списка

​Основные вкладки​ это мы уже​ а именно с​При работе в программе​ ниже 2007 те​ привести к нежелаемым​

​ столбец и введем​1​ списка (A2:A5) и​ содержит название столбца.​ нужна кнопка «Поле​ задачи.​ lReply = vbYes​ в раскрывающемся списке.​ForeColor​ ячейкой, где отображается​ программно разместить в​установите флажок для​ делали ранее с​ использованием ActiveX. По​ Microsoft Excel в​

​ же действия выглядят​ результатам.​ в него такую​- размер получаемого​ введите в поле​ На появившейся после​ со списком» (ориентируемся​

​Создаем стандартный список с​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​Выделяем диапазон для выпадающего​(Цвет текста), щелкните​ номер элемента при​ ячейках, содержащих список​ вкладки​ обычными выпадающими списками.​ умолчанию, функции инструментов​ таблицах с повторяющимися​ так:​Итак, для создания​

​ страшноватую на первый​ на выходе диапазона​ адреса имя для​ превращения в Таблицу​ на всплывающие подсказки).​ помощью инструмента «Проверка​ 1, 1) =​ списка. В главном​

​ стрелку вниз, откройте​ его выборе из​ проверки данных.​Разработчик​В первой ячейке создаём​ разработчика отсутствуют, поэтому​ данными, очень удобно​Второй​ выпадающего списка необходимо:​ взгляд формулу:​ по горизонтали, т.е.​ этого диапазона (без​

​ вкладке​Щелкаем по значку –​ данных». Добавляем в​ Target End If​ меню находим инструмент​ вкладку​ списка. Введите номер​Выберите столбец, который можно​и нажмите кнопку​

Создание выпадающего списка с помощью инструментов разработчика

​ список точно таким​ нам, прежде всего,​ использовать выпадающий список.​: воспользуйтесь​1.​=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))​ один столбец​ пробелов), например​Конструктор (Design)​ становится активным «Режим​ исходный код листа​ End If End​ «Форматировать как таблицу».​Pallet​

​ ячейки, где должен​ скрыть на листе,​ОК​ же образом, как​ нужно будет их​ С его помощью​

​Диспетчером имён​Создать список значений,​или, соответственно,​Теперь выделите ячейки, где​Стажеры,​можно изменить стандартное​ конструктора». Рисуем курсором​ готовый макрос. Как​ If End Sub​Откроются стили. Выбираем любой.​(Палитра) и выберите​ отображаться номер элемента.​ и создайте список,​.​

​ делали это ранее,​ включить. Для этого,​ можно просто выбирать​(Excel версий выше​ которые будут предоставляться​

​=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))​ вы хотите создать​и нажмите на​ имя таблицы на​

​ (он становится «крестиком»)​ это делать, описано​Сохраняем, установив тип файла​ Для решения нашей​ цвет.​Например, в ячейке C1​ введя по одному​

​В разделе​ через проверку данных.​ переходим во вкладку​ нужные параметры из​ 2003 — вкладка​

​ на выбор пользователю​При всей внешней жуткости​

​ выпадающие списки, и​Enter​ свое (без пробелов!).​ небольшой прямоугольник –​ выше. С его​ «с поддержкой макросов».​ задачи дизайн не​

Связанные списки

​Связь с ячейкой для​ отображается значение 3, если​ значению в ячейки.​Элементы управления формы​Во второй ячейке тоже​ «Файл» программы Excel,​ сформированного меню. Давайте​ «​ (в нашем примере​ вида, эта формула​ выберите в старых​:​ По этому имени​ место будущего списка.​ помощью справа от​Переходим на лист со​ имеет значения. Наличие​

​ отображения значения, выбранного​ выбрать пункт​Примечание:​выберите элемент управления​ запускаем окно проверки​ а затем кликаем​

​ выясним, как сделать​Формулы​ это диапазон​ делает одну простую​ версиях Excel в​

​Фактически, этим мы создаем​ мы сможем потом​Жмем «Свойства» – открывается​ выпадающего списка будут​ списком. Вкладка «Разработчик»​

​ заголовка (шапки) важно.​ в списке​Фруктовое мороженое​ Можно также создать список​Список (элемент управления формы)​ данных, но в​ по надписи «Параметры».​

​ раскрывающийся список различными​

​» — группа «​M1:M3​ вещь — выводит​ меню​ именованный динамический диапазон,​ адресоваться к таблице​ перечень настроек.​ добавляться выбранные значения.Private​

​ — «Код» -​

​ В нашем примере​Щелкните свойство​, так как это​ на другом листе​.​ графе «Источник» вводим​В открывшемся окне переходим​ способами.​Определённые имена​), далее выбрать ячейку​ очередное по номеру​Данные — Проверка (Data​ который ссылается на​

​ на любом листе​

lumpics.ru>

Функции даты и времени

Данный раздел формул очень интересен и полезен. При помощи их можно проводить быстрый анализ какой-нибудь информации либо вытаскивать определенные данные из указанной даты.

• ДЕНЬ – определяется день в указанной дате;
• МЕСЯЦ – определяется месяц в указанной дате;
• ГОД – определяется год в указанной дате;
• СЕГОДНЯ – вывод текущей даты;
• НОМНЕДЕЛИ – вывод номера недели на основании указанной даты;
• ТДАТА – вывод текущей даты и текущего времени;
• ЧАС – определяется какой час указан в определенной дате;
• МИНУТЫ – определяется сколько минут указано в определенной дате;
• СЕКУНДЫ – определяется сколько секунд указано в определенной дате;
• ДЕНЬНЕД – вычисляется порядковый номер дня недели (отсчет начинается с воскресенья, а не с понедельника).

Кроме этого, есть и более сложные формулы. К ним относятся:

• РАЗНДАТ – происходит расчет количества лет, месяцев и дней между указанными датами;
• ДНИ – происходит расчет количества дней между указанными датами (функция появилась в 2013 году);
• ЧИСТРАБДНИ – происходит расчет количества рабочих дней между указанными датами;
• ДЕНЬНЕД – преобразование обычной даты в числовом формате в порядковый номер недели;
• РАБДЕНЬ – вывод даты, которая отстает или опережает указанное количество дней.

Более подробно о последней формуле можно прочитать на официальном сайте Microsoft.

Синтаксис данной функции следующий.

А примеры довольно простые.

Как получить первое минимальное значение?

Для демонстрации решения задачи создадим простую таблицу:

В этом примере данные таблицы где нужно найти значение находятся в диапазоне A6:B18. Как видно на рисунке в столбце A содержаться значения, а в столбце B соответствующие им суммы. Кроме того, видно, что среди сумм встречается несколько минимальных значений разбросанных по разным строкам.

Чтобы получить значение из столбца A которому соответствует первая наименьшая сумма в столбце B выполним 2 простых шага действий:

1. В ячейке B3 введите следующую формулу:
2. Подтвердите ввод формулы комбинацией горячих клавиш CTRL+SHIFT+Enter, так как она должна выполнится в массиве. Если все сделано правильно в строке формул появятся фигурные скобки {}.

В результате мы получили значение, соответствующее первой наименьшей сумме.

Детальный разбор формулы для первого наименьшего значения

Функция ИНДЕКС является самой главной в этой формуле. Ее номинальная задача – это поиск значения в указанной таблице (диапазон A6:A18 указанный в первом аргументе функции) на основе координат, указанных в ее втором (номер строки таблицы) и третьем (номер столбца) аргументах. Так как таблица, которую указано для работы функции ИНДЕКС имеет только один столбец A, то третий аргумент в параметрах – пропущен (необязателен). В тоже время во втором аргументе сразу используется несколько функций, работающих с диапазоном B6:B18.

В первом аргументе функции ЕСЛИ тестирует: является ли значение в каждой ячейке диапазона B6:B18 наименьшим числом. Таким образом в памяти создается массив логических значений ИСТИНА и ЛОЖЬ. В данном примере массив содержит только три элемента с положительным результатом теста, так как столько же содержит одинаковых минимальных значений исходная таблица. Если результат положительный формула переходит к следующему вычислительному этапу, а если отрицательный функция возвращает в массив памяти пустое текстовое значение.

Следующий вычислительный этап формулы – это определение, какие номера строк содержат эти минимальные суммы. Данный этап нам необходим для того, чтобы определить первое минимальное значение в диапазоне B6:B18. Реализовывается такая задача с помощью функции СТРОКА, которая создает в памяти программы следующий массив, состоящий из номеров строк. От этих номеров следует вычесть номер первой строки с которой начинается диапазон исходной таблицы

Это очень важно, ведь функция ИНДЕКС работает не с номерами строк листа Excel, а с номерами строк таблицы, указанной в ее первом аргументе. Таким образом, чтобы получить истинный номер исходной таблицы мы отнимаем от каждого номера строки листа, то количество строк, которое находится выше положения таблицы на листе

Таким образом в памяти программы сформируется массив, состоящий из номеров строк. Далее функция МИН возвращает наименьший номер строки таблицы, которая содержит минимальное значение. Этот номер использует в качестве второго аргумента функция ИНДЕКС, которая поэтому же номеру строки возвращает нам значение из диапазона A6:B18.

Поиск и подстановка по нескольким условиям

Постановка задачи

​ понять, что она​Минусы​ цену заданного товара​(для строки должны​Скопировав формулу в остальные​​ и ее можно​​ очередь будет выполнена​​=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)​​ между 1 и​ применении, может стать​«Другие функции…»​ чтобы после ввода​,​ вручную вбить в​ возвращать в ячейку​ и команду ВСТАВИТЬ.​ сотен позиций, целесообразно​ не просто считает​: Работает только с​ (​ одновременно выполняться два​ ячейки таблицы, можно​ применять в различных​ функция​Введите номер месяца в​

​ 254 (или от​ очень хорошим помощником​.​ номера торговой точки​

​«отлично»​ группу полей​ ошибку.​​У нас появилась дополнительная​​ использовать СУММЕСЛИ.​ сумму, но еще​​ числовыми данными на​​Нектарин​ критерия) (см. статью Сложение​​ увидеть, что на​​ ситуациях. Рассмотрим еще​CHOOSE​ ячейку C2, и​ 1 до 29​ для выполнения поставленных​Производится запуск​ в определенную ячейку​

Способ 1. Дополнительный столбец с ключом поиска

​).​«Значение»​Следующая группа аргументов​ строчка. Сразу обращаем​Если к стандартной записи​​ и подчиняется каким-либо​​ выходе, не применима​) в определенном месяце​ чисел с несколькими​ отлично сдал один​ один пример. В​(ВЫБОР), которая вернет​

​ функция​ в Excel 2003​ задач. При использовании​Мастера функций​ листа отображалась сумма​Выделяем первую ячейку в​наименование месяцев

Причем​«Значение»​ внимание, что диапазон​

​ команды СУММЕСЛИ в​ логическим условиям.​​ для поиска текста,​​ (​ критериями (Часть 2.​​ человек, а на​​ таблице ниже приведены​ требуемый диапазон для​CHOOSE​

​ и более ранних​​ её в комбинации​, в котором в​ выручки за все​

​ колонке​​ каждому полю должен​. Она может достигать​ условий и суммирования​ конце добавляются еще​Функция СУММЕСЛИ позволяет суммировать​ не работает в​Январь​ Условие И)​ оценки хорошо и​ результаты переаттестации сотрудников​ суммирования, соответствующий выбранному​

Способ 2. Функция СУММЕСЛИМН

​(ВЫБОР) вычислит номер​ версиях).​ с другими операторами​ разделе​ дни работы указанного​«Описание»​​ соответствовать отдельный месяц,​​ количества​ автоматически расширился до​ две буквы –​ ячейки, которые удовлетворяют​ старых версиях Excel​), т.е. получить на​Пример1, суммирование Чисел​ плохо по два​ фирмы:​ магазину.​ финансового квартала в​index_num​ возможности существенно увеличиваются.​«Ссылки и массивы»​

​ магазина. Для этого​​и переходим при​ то есть, в​254​ 15 строки.​ МН (СУММЕСЛИМН), значит,​

​ определенному критерию (заданному​​ (2003 и ранее).​ выходе​ попадающих в определенный​ человека.​В столбец C нам​=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))​ ячейке C3.​

Способ 3. Формула массива

1. ​=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))​Функция​ введён в функцию​
2. ​Вчера в марафоне​ наименование​ использовать комбинацию операторов​
3. ​ о котором уже​«Значение1»​​ обязательным является аргумент​​ вставляем их в​ несколькими условиями. Она​ следующие:​ связку функций​

​, но автоматически, т.е.​ соответствовать 2-м критериям:​

​ и третьего аргументов​ экзамена, который должен​Это пример ситуации, когда​CHOOSE​ в виде числа,​30 функций Excel за​«ВЫБОР»​СУММ​ шел разговор выше,​записываем​​«Значение1»​​ общий перечень. Суммы​ применяется в случае,​Диапазон – ячейки, которые​ИНДЕКС (INDEX)​ с помощью формулы.​ быть больше Критерия1​ функции​ содержать всего два​ гораздо эффективнее использовать​(ВЫБОР) может работать​ формулы или ссылки​ 30 дней​и выделить его.​

​и​​ в окно аргументов​«Январь»​. В данной группе​ в итоговых ячейках​

​ когда нужно задать​​ следует оценить на​и​ ВПР в чистом​И​ЕСЛИ​ варианта:​ другие функции, такие​ в сочетании с​ на другую ячейку.​мы выяснили детали​ Щелкаем по кнопке​ВЫБОР​ оператора​

planetaexcel.ru>

Условная функция ЕСЛИ()

В этой функции проверяется истинность одного или нескольких логических выражений и выбирается дальнейшее действие. В логических выражениях используют знаки операций отношения между сравниваемыми величинами.

Простое условие

Что же делает функция ЕСЛИ()? Посмотрите на схему. Здесь приведен простой пример работы функции при определении знака числа а.

Блок-схема “Простое условие”. Определение отрицательных и неотрицательных чисел

Условие а>=0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное. Ниже схемы приведена запись формулы в Excel. После условия через точку с запятой перечисляются варианты действий. В случае истинности условия, в ячейке отобразится текст “неотрицательное”, иначе – “отрицательное”. То есть запись, соответствующая ветви схемы «Да», а следом – «Нет».

Если результатом должны быть данные, полученные в результате вычислений, то смотрим следующий пример. Выполним увеличение неотрицательного числа на 10, а отрицательное оставим без изменений.

Блок-схема “Простое условие”. Расчет данных

На схеме видно, что при выполнении условия число увеличивается на десять, и в формуле Excel записывается расчетное выражение А1+10 (выделено зеленым цветом). В противном случае число не меняется, и здесь расчетное выражение состоит только из обозначения самого числа А1 (выделено красным цветом).

Это была краткая вводная часть для начинающих, которые только начали постигать азы Excel. А теперь давайте рассмотрим более серьезный пример с использованием условной функции.

Решение:

Решение данной задачи видно на рисунке ниже. Но внесем все-таки ясность в эту иллюстрацию. Основные исходные данные для решения этой задачи находятся в столбцах А и В. В ячейке А5 указано пограничное значение дохода при котором изменяется ставка налогообложения. Соответствующие ставки указаны в ячейках В5 и В6. Доход фирм указан в диапазоне ячеек В9:В14. Формула расчета налога записывается в ячейку С9: =ЕСЛИ(B9>A$5;B9*B$6;B9*B$5). Эту формулу нужно скопировать в нижние ячейки (выделено желтым цветом).

В расчетной формуле адреса ячеек записаны в виде A$5, B$6, B$5. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки.

Логическая операция И()

Например: Рассмотрим электронную таблицу “Ведомость сдачи вступительных экзаменов”. Для зачисления абитуриента в ВУЗ, ему необходимо преодолеть проходной балл, и по математике отметка должна быть выше 70 баллов. Посмотрите внимательно на рисунок ниже.

В этом примере функция ЕСЛИ() использует составное условие, связанное логической операцией И()

Обратите внимание: абитуриент Петров не зачислен, хотя сумма его баллов равна проходному

Почему так произошло? Посмотрим внимательно на условие в нашей формуле =ЕСЛИ(И(E6>=D2;B6>70);”зачислен”;”не зачислен”). Логическая операция И() требует выполнения всех условий, но у нас выполняется только одно. Второе условие B6>70 не выполнено, поэтому составное условие принимает значение «ложь». И на экран выводится сообщение «не зачислен» (вспоминаем схему – ветвь «нет»).

1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.

2. Определите скидку (в процентах), используя функцию ЕСЛИ(). Если дата продажи попадает в период праздничной распродажи, то назначается скидка, иначе скидка равняется нулю. При задании условий используйте логическую функцию И().

3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%).

Решение:

Для проведения расчетов необходимо вписать следующие формулы:

• В ячейке Е7: =B7*C7
• В ячейке F7: =ЕСЛИ(И(D7>=D$4;D7

Абитуриент Сидоров зачислен, хотя не набрал проходной балл. Вот формула =ЕСЛИ(ИЛИ(B7>60;E7>D2;);”зачислен”;”не зачислен”). Здесь использована операция ИЛИ(), поэтому достаточно выполнение хотя бы одного условия. Что и произошло, первое условие B7>60 истинно. Оно привело к выводу сообщения о зачислении абитуриента.

1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.

2. Определите скидку (в процентах), используя функцию ЕСЛИ(). Если дата продажи совпадает с датами распродаж, то назначается скидка, иначе скидка равняется нулю. При задании условий используйте логическую функцию ИЛИ().

3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%)

Решение:

Для проведения расчетов необходимо вписать следующие формулы:

• В ячейке Е7: =B7*C7
• В ячейке F7: =ЕСЛИ(ИЛИ(D7=D$4;D7=E$4;D7=F$4);B$4;0)
• В ячейке G7: =E7*(1-F7)

и скопировать по соответствующим столбцам до 15 строки включительно.

Функция ЕСЛИ в Excel (общие сведения)

Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:

1. Проверить всю колонку А на предмет наличия четных чисел.
2. Если четное число обнаружено, сложить такие-то значения.
3. Если четное число не обнаружено, то выдать надпись «не обнаружено».
4. Проверить получившееся число, является ли оно четным. 
5. Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

И пусть это только гипотетическая ситуация, которая вряд ли будет нужной в реальной жизни, но выполнение любой задачи обязательно подразумевает наличие похожего алгоритма. Перед тем, как использовать функцию ЕСЛИ, нужно иметь в голове четкое представление о том, какого результата нужно достичь. 

Синтаксис функции ЕСЛИ с одним условием

Любая функция в Ecxel выполняется с помощью формулы. Шаблон, по которому нужно передавать функции данные, называется синтаксисом. В случае с оператором ЕСЛИ, формула будет в таком формате.

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)

Давайте рассмотрим синтаксис более подробно:

1. Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
2. Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
3. Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.

Вот пример для наглядности.

1

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

Вот еще одна ситуация. Для получения права сдавать экзаменационную сессию учащимися должна быть пройдена зачетная. Студентам получилось одержать зачет по всем предметам, и теперь остался последний, который оказался решающим. Наша задача – определить, какие из студентов допущены к экзаменам, а какие – нет.

2

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

Синтаксис функции ЕСЛИ с несколькими условиями

Нередко недостаточно одного критерия, на предмет соответствия которому нужно проверять значение. Если надо учитывать больше одного варианта, можно вкладывать функции ЕСЛИ одна в другую. Получится несколько вложенных функций.

Чтобы было более наглядно, вот синтаксис.

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

В этом случае функция проверит сразу два критерия. Если первое условие оказывается истинным возвращается значение, полученное в результате совершения операции в первом аргументе. Если нет – осуществляется проверка на соответствие второму критерию.

Вот пример.

3

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

4

Как видим, здесь было добавлено еще одно условие, но принцип не изменился. Так можно сразу несколько критериев проверять.

Примеры использования функции СУММЕСЛИ в Excel с несколькими условиями

​, в поле​ аргументов указываются те​ изменились. Функции среагировали​ не один критерий.​ основании критерия (заданного​ПОИСКПОЗ (MATCH)​ виде тут не​

​ одновременно меньше Критерия2;​можно подставлять новые​Сдал​ как​ функцией​index_num​

СУММЕСЛИ и ее синтаксис

​ нашей операционной среды​«OK»​.​ВЫБОР​«Значение2»​

1. ​ значения, которым будет​ на появление в​Аргументов у СУММЕСЛИМН может​ условия).​
2. ​в качестве более​ поможет, но есть​Пример2, суммирование продаж определенного магазина​ функции​
3. ​или​INDEX​WEEKDAY​(номер_индекса) будет округлен​

​ с помощью функции​.​Выделяем ячейку, в которой​.​—​ соответствовать номер индекса​ диапазоне еще одного​ быть сколько угодно,​

Как работает функция СУММЕСЛИ в Excel?

​ мощной альтернативы ВПР​ несколько других способов​ в первом квартале.​ЕСЛИ​Не сдал​(ИНДЕКС) и​(ДЕНЬНЕД), чтобы вычислить​

​ до ближайшего меньшего​INFO​Активируется окошко аргументов оператора​ будет выводиться результат​В поле​«Февраль»​ предыдущего аргумента. То​ продавца-женщины.​ но минимум –​ ячейки из диапазона​ я уже подробно​ решить эту задачу.​

​Условие ИЛИ​, тем самым расширяя​. Те, кто набрал​MATCH​ грядущие даты. Например,​ целого.​

​(ИНФОРМ) и обнаружили,​

1. ​ВЫБОР​ в виде суммы.​«Номер индекса»​и т. д.​ есть, если в​Аналогично можно не только​ это 5.​
2. ​ будут выбраны (записывается​ описывал (с видео).​Это самый очевидный и​(для строки должен​ число условий, которое​
3. ​ более 45 баллов​(ПОИСКПОЗ). Далее в​ если Вы состоите​аргументами​ что она больше​. В поле​

​ После этого щелкаем​указываем ссылку на​После выполнения указанной задачи​ качестве аргумента​ добавлять, но и​Диапазон суммирования. Если в​

​ в кавычках).​ В нашем же​ простой (хотя и​ выполняться хотя бы​ формула может обработать.​ – сдали экзамен,​ нашем марафоне мы​ в клубе, который​value​ не сможет помочь​«Номер индекса»​

Функция СУММЕСЛИ в Excel с несколькими условиями

​ по уже знакомому​ первую ячейку столбца​ щелкаем по кнопке​«Номер индекса»​ удалять какие-либо строки​ СУММЕСЛИ он был​Диапазон суммирования – фактические​ случае, можно применить​ не самый удобный)​ один из 2-х​

Синтаксис с использованием функции по нескольким критериям

​ Таким образом, Вы​ остальные нет.​ увидим, как они​ собирается каждый понедельник​

1. ​(значение) могут быть​ нам в вопросах,​указываем ссылку на​ нам значку​«Оценка»​«OK»​выступает число​ (например, при увольнении​
2. ​ в конце, то​ ячейки, которые необходимо​ их для поиска​ способ. Поскольку штатная​
3. ​ или более критериев).​ можете создать нужное​Выделите ячейку, в которую​ работают.​
4. ​ вечером, то, зная​ числа, ссылки на​ связанных с памятью.​ ту ячейку листа,​
5. ​«Вставить функцию»​, в которой содержится​внизу окна.​«3»​

​ сотрудника), изменять значения​ здесь он стоит​ просуммировать, если они​ по нескольким столбцам​ функция​Пример1, суммирование продаж​ количество вложений. Правда​

Пример использования

​ необходимо ввести формулу.​Урок подготовлен для Вас​ сегодняшнюю дату, Вы​ ячейки, именованные диапазоны,​ Ни с нашей,​ в которую будем​

• ​.​
• ​ балл.​

​Как видим, сразу в​, то ему будет​

​ (заменить «январь» на​

• ​ на первом месте.​ удовлетворяют критерию.​
• ​ в виде формулы​ВПР (VLOOKUP)​ товаров «яблоки» (критерий1) ​
• ​ есть очевидный недостаток​
• ​ В нашем случае​ командой сайта office-guru.ru​ можете рассчитать дату​
• ​ функции или текст.​ ни с памятью​

​ вводить номер торговой​Активируется окошко​Группу полей​

СУММЕСЛИ в Excel с динамическим условием

​ внесено, как аргумент​ новые заработные платы)​ ячейки, которые необходимо​ всего 3 аргумента.​Выделите пустую зеленую ячейку,​ по одному столбцу,​

​ товаров «груши» (так называемый​ 3-5 вложений формула​Введите в нее выражение:​Перевел: Антон Андронов​На рисунке ниже представлены​ более ранних версиях​На пятый день марафона​ отображения общей суммы​

​. На этот раз​заполняем следующим образом:​ в первом действии,​«Значение3»​ и т.п.​ просуммировать.​ Но иногда последний​ где должен быть​ а не по​

exceltable.com>

​ множественный выбор как​

• Excel если несколько условий
• Excel счетесли несколько условий
• Несколько условий в excel счетесли в excel
• Счетесли в excel примеры с двумя условиями
• Эксель функция если с несколькими условиями
• Счетесли excel несколько условий
• Поиск значения в excel по нескольким условиям в
• Несколько условий if excel
• Excel поиск значения по нескольким условиям в excel
• Поиск в excel по нескольким условиям в excel
• Функция ранг в excel примеры
• Функция или в excel примеры