Top-office11.ru

IT и мир ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция ЕСЛИ в Excel

Функция ЕСЛИ в Excel

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

Речь пойдет про функцию ЕСЛИ в Excel.

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

X или Y

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

Количество ячеек, которые содержат x или y

Когда вы подсчитывать ячейки с критерием «или», вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат «abc» или «def», вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и «abc» и «def».

Решение одной формулой

Для решения одной формулойц, вы можете использовать комбинацию СУММПРОИЗВ с ЕЧИСЛО + НАЙТИ. Формула в ячейке E4 будет:

Эта формула основана на формуле, которая находит текст внутри ячейки:

При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и еще для «def»), мы получим два массива.

Далее мы складываем эти массивы вместе (+), сложение создаст новый единый массив чисел. Каждое число в этом массиве является результатом сложения истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит следующим образом:

Нам нужно сложить эти цифры, но мы не хотим, чтобы удвоился счет. Таким образом, мы должны убедиться, что любое значение больше нуля. Чтобы сделать это, мы вернем все значения, которые больше 0, в Истина или Ложь, а затем с помощью двойного отрицания (—) переведем массив в формат 1 и 0.

И, наконец, СУММПРОИЗВ суммирует полученные числа.

Вспомогательный столбец решений

Со вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложная. Мы можем использовать СЧЁТЕСЛИ с двумя значениями (при условии, как «бесконечное множество»). Формула:

СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для «abc» и подсчет на «def». Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат «истина/ложь» с «>0». Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (—).

Итоговый результат равен 1 или 0 для каждой ячейки. Чтобы получить в общей сложности для всех ячеек в диапазоне, вам нужно просуммировать вспомогательный столбец.

Формула суммы в Excel

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

  1. Откройте ту таблицу, в которой нужно произвести расчет.
  2. В свободной ячейке поставьте курсор и введите знак «=» без кавычек. Программа автоматически активирует функцию, предназначенную для суммирования чисел в ячейках таблицы.
  3. Щелкните левой кнопкой мыши по той ячейке, данные которой будут применены в расчете. Там, где поставлен знак «=», появится значение из цифр и букв, обозначающее местоположение числа в таблице. При этом буква – это столбец, а цифра – строка.

Формула суммы в Excel

Сложение ячеек Excel формулой

Чтобы найти сумму чисел в выбранных ячейках, нажмите на клавишу Enter . В поле, где складывались значения расположения данных, появится число – сумма данных.

Простое сложение в Excel

При пустом значении ячейки или текста в ней программа автоматически проигнорирует данные. В верхней строке возможно вводить формулу вручную после знака «=». Буквенно-цифровое значение ячейки необходимо складывать с помощью «+», при этом неважно, верхний или нижний регистр используется. Для получения результата также нажмите на клавишу ввода.

9 способов как используется функция БДСУММ в Excel

Function BDSUMM 9 способов как используется функция БДСУММ в Excel Добрый день!

Продолжу свое стремление описать разнообразие функций в Excel и следующей на рассмотрении у нас функция БДСУММ. Это еще один из представителей функций суммирования, но со своими специфическими условиями. Функция БДСУММ в Excel ищет и суммирует числа в вашей таблице по определенным вами критериям, это ее основное свойство.

Положа руку на сердце могу сказать, что много расчётов и вычислений можно сделать и без нее используя разнообразные функции массивов, функцию СУММЕСЛИМН или СУММПРОИЗВ, но если же вам нужно сделать сложную выборку используя «подстановочные» знаки, то тогда вам точно нужно использовать героя нашей статьи.

Для начала рассмотрим синтаксис, который использует функция БДСУММ в Excel:

=БДСУММ(диапазон вашей базы данных; поле поиска; условие поиска), где

Function BDSUMM 2 9 способов как используется функция БДСУММ в Excel

  • Диапазон вашей базы данных – является обязательным критерием и указывается диапазон, из которого и будут по критериям изыматься нужные вам значения, обязательное условие, что бы первая строка содержала в себе заголовок вашей таблицы;
  • Поле поиска – это обязательный критерий, указывается для определения, по которому полю производить поиск и суммирование чисел. Указывать можно как название поля, то есть текстовое значение, типа «Продукт», «Страна», с обязательным взятием аргумента в кавычки, так и числовое значение, типа, 1,2,3… для определения номера поля или просто указать ссылку на нужное поле, решать вам;
  • Условие поиска – это обязательный аргумент, который содержит в себе диапазон с указанными в нём критериями для суммирования значений. Аналогичная структура таблицы используется при создании расширенного фильтра.

При работе с функцией БДСУММ стоить отметить несколько условий, на которые стоит обращать внимание при работе:

  • При выполнении работы над целым столбиком заполненным данными, обязательно стоит вставить пустую строку под заголовками столбиков в указанном диапазоне критериев;
  • Диапазон критериев не должен иметь пересечения со списком;
  • Хотя данные для формирования диапазона условий вы можете помещать в любое место на листе, но тем не менее, помещать его после списка не следует, так как данные которые мы добавляем в список, будут вставляться в первую строку после списка и если в строке есть данные, то добавить новые не получится;
  • Можно использовать для условий любой диапазон, который содержит, как минимум один заголовок и хотя бы одну из ячеек, которая расположена под заголовком и содержит в себе условие.

Итак, теоретическую часть, я считаю выполненной, приступим теперь к практическому применению функции БДСУММ в своей работе, для этого рассмотрим несколько примеров для выполнения, я примеры сделал по принципу расширенного фильтра, но вместо сбора значений по критерию будет суммирование:

  • С одним числовым критерием;
  • С одним текстовым критерием;
  • Суммирование по двум критериям по разным столбцам;
  • Суммирование по одному из двух условий в одном столбике;
  • Суммирование по одному из двух условий в двух разных столбиках;
  • Суммирование по двум текстовым критериям по двум столбикам;
  • Использование результата формулы для получения критерия отбора и суммирования;
  • Суммирование по трём критериям;
  • Суммирование по текстовому критерию с учётом регистра.

Функция БДСУММ с одним числовым критерием

Итак, для начала рассмотрим простой пример с одним числовым критерием, для этого выберем столбик «Урожай» и укажем что нам надо деревья с урожайность «>=10». Что бы получить результат нам нужна формула такого вида (советую использовать абсолютные ссылки):

=БДСУММ($B$6:$G$12;E6;E2:E3),

где, $B$6:$G$12 диапазон в котором мы будем суммировать, E6 – столбик в котором мы будем суммировать и E2:E3 диапазон в которые мы ввели критерии для суммирования. В итоге формула нашла 3 позиции на общую сумму 34.

Для получения аналогично результата вы можете воспользоваться также такими формулами:

=СУММЕСЛИ(E7:E12;»>=10″)

Function BDSUMM 3 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(E7:E12;E3)

Функция БДСУММ с одним текстовым критерием

Теперь рассмотрим, как ведет себя функция БДСУММ с текстовыми критериями, в общем всё остается так и в предыдущем примере за исключением того как указывается текстовый критерий, а он указывается только в таком виде: =»=с.Серово» и тогда результат у вас получится, иначе формула не сможет распознать ваш критерий. Теперь подставляем этот критерий в формулу и получаем:

=БДСУММ($B$6:$G$12;E6;C2:C3), как видим произошло только изменения диапазона критерия.

Для получения схожего результата вам пригодится функция СУМЕСЛИ:

Function BDSUMM 4 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(C7:C12;»с.Серово»;E7:E12)

Суммирование по двум критериям по разным столбцам

Пример усложняется применением двух критериев, но ничего принципиально нового мы применять не будем, укажем текстовый критерий «с.Серово» и числовой критерий «>=10», оставив поле суммирование «Урожай», мы получим изменение формулы только по последнему аргументу, как результат. Теперь наша формула будет выглядеть так:

=БДСУММ($B$6:$G$12;E6;C2:E3), снова видите изменения только адреса диапазона критерия.

Альтернативный вариант можно получить с помощью функции СУММЕСЛИМН и так:

=СУММЕСЛИМН(E7:E12;C7:C12;C3;E7:E12;E3)

Function BDSUMM 5 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИМН(E7:E12;C7:C12;»с.Серово»;E7:E12;»>=10″)

Суммирование по одному из двух условий в одном столбике

Рассмотрим еще один вариант как применяется функция БДСУММ, но сейчас будем использовать не единичный критерий, а двойной, но для одного поля. Обязательно два критерия должны быть в разных строках. Суть формулы сводится к тому что, она проходит один и тот же диапазон два раза считая каждый из критериев отдельно. Для этого примера формула будет выглядеть так:

=БДСУММ($B$6:$G$12;E6;C2:C4), здесь снова изменяем диапазон критерия, но не в ширину, а в высоту.

Также заменителем, вы можете использовать сумму функции СУММЕСЛИ:

Function BDSUMM 6 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ(C7:C12;C3;E7:E12)+СУММЕСЛИ(C7:C12;C4;E7:E12).

Суммирование по одному из двух условий в двух разных столбиках

Пятым примером применения БДСУММ в Excel станет суммирование по одному из двух условий в двух разных столбиках и обязательно аргументы нужно размещать все в новых строках, это позволит их суммировать по очереди. Для этих целей нам понадобится формула:

Function BDSUMM 7 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;C2:D4), принцип формирования формулы сохраняется, окромя диапазона критерия, который включает в себя три строки: заголовок и два критерия.

Суммирование по двум текстовым критериям по двум столбикам

В данном примере работы БДСУММ в Excel рассмотрим практически полный аналог ранее рассматриваемого примера, когда были два критерия в двух столбика, но там были числовой и текстовый критерий, а здесь рассмотрим суммирование по двум текстовым критериям и по двум столбикам. Используем критерии «=»=с.Иваново»» и «=»=Вишня»», которые мы и укажем в диапазоне критериев. Значит наша формула будет иметь следующий вид:

Function BDSUMM 8 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;B2:C3).

Использование результата формулы для получения критерия отбора и суммирования

В данном примере функции БДСУММ я использую формулу для определения критерия отбора и суммирования, в данном случае найдем какие же деревья нам приносят доход и для определения условного аргумента возьмем среднее значение по продажам плодов с деревьев и всё что выше среднего показателя нас интересует. Для определения среднего значение которое станет нашим критерием «ИСТИНА» мы создаем формулу статистического направления:

Function BDSUMM 9 9 способов как используется функция БДСУММ в Excel

=G7>СРЗНАЧ($G$7:$G$12), не забываем об абсолютных ссылок для закрепления диапазона, что бы при переборе формулой значений они не сползли вниз, а вот значение G7 должно скользить по всему диапазону для определения «ЛОЖЬ» это или «ИСТИНА». Очень важно еще то чтобы названия заголовков не дублировались, имели отличие, поэтому поле критериев я назову «Среднее». И тогда формула начнёт работать, она переберет весь диапазон $G$7:$G$12 на наличие среднего значения и при получении положительного результата «ИСТИНА» она будет суммировать. С этой работой справится формула следующего вида:

=БДСУММ($B$6:$G$12;G6;$G$2:$G$3)

А если вам очень интересно альтернативное решение вопроса, то тогда испробуйте вариант с функцией СУММЕСЛИ в таком виде:

Function BDSUMM 10 9 способов как используется функция БДСУММ в Excel

=СУММЕСЛИ($G$7:$G$12;»>»&СРЗНАЧ($G$7:$G$12))

Функция БДСУММ по трём критериям

В этом примере посчитаем среднее по продажам с выращенных плодов в двоих сёлах: «с.Иваново» и «с.Уютное». Основную идею отбора по критериям я уже описывал, поэтому повторятся не буду, просто скажу, что это будет соединение ранее рассмотренных критериев. Для получения результата нам нужна функция БДСУММ в таком виде:

Function BDSUMM 11 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;G6;$C$2:$G$4)

Суммирование по текстовому критерию с учётом регистра

Как я упоминал ранее, функция БДСУММ может производить поиск не только с подстановочными символами, но и с учётом регистра букв, вот сейчас именно такой вариант. Для начала определим условие по отбору критерия, если встречается название «с.ИВАНОВО», заглавными, то мы производим суммирование, для определения этого критерия нам нужна формула:

Function BDSUMM 12 9 способов как используется функция БДСУММ в Excel

=СОВПАД(«с.ИВАНОВО»;C7) А вот теперь мы можем прописать функцию БДСУММ, которая проверит диапазон на наличие указанного критерия и при получении значения «ИСТИНА» произведет суммирование. В примере я указал специально один раз по условию, и как видим, формула успешно отобрала все населенные пункты и нашла нужный и получили результат «9». Для этого была использована формула:

Function BDSUMM 13 9 способов как используется функция БДСУММ в Excel

=БДСУММ($B$6:$G$12;D6;$C$2:$C$3) Ну что же, я думаю, что функция БДСУММ в Excel, мною была описана деталь и во многих подробностях, поэтому вопросов будет мало, а пользы много. Если у вас возникли вопросы пишите комментарии, жду ваши лайки и отзывы. О других функциях вы можете ознакомиться в «Справочнике функций» на моем сайте.

Успехов вам в ваших делах!

«За деньги можно, конечно, купить очаровательного пса, но никакие деньги не смогут заставить его радостно вилять хвостом.
»
Д. Биллингс

Функция MOD (ОСТАТ) в Excel. Как использовать?

Функция ОСТАТ (MOD) используется в Excel, когда нам нужно получить остаток при делении двух чисел.

  1. Что возвращает функция
  2. Синтаксис
  3. Аргументы функции
  4. Дополнительная информация
  5. Примеры использования функции ОСТАТ в Excel

Что возвращает функция

Возвращает число, равное остатку при делении одного числа на другое. Например, у вас есть число “20”, вы хотите узнать сколько останется при целом делении на число “7”. Делить число “20” на “7” целыми числами можно только на “2”. Таким образом, “2” умножить на “7” равно “14”. Остаток между “20” и “14” равен “6”. Результат вычисления по функции будет равен “6”.

Синтаксис

=MOD(number, divisor) – английская версия

=ОСТАТ(число;делитель) – русская версия

Аргументы функции

  • number (число) – число, для которого мы хотим вычислить остаток;
  • divisor (делитель) – число, на которое мы хотим поделить аргумент number (число). Если divisor (делитель) равен нулю, то функция ОСТАТ выдаст ошибку.

Дополнительная информация

  • Если аргумент функции divisor (делитель) равен нулю, то функция выдаст ошибку;
  • Результат функции всегда имеет тот же знак, что и у делителя (divisor) (то есть положительный или отрицательный).

Примеры использования функции ОСТАТ в Excel

Функция MOD (ОСТАТ) в Excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Построение графиков функций

Графики функций – тема, косвенно связанная с использованием формул в Excel, поскольку подразумевает не добавление их в таблицу, а непосредственное составление таблицы по формуле, чтобы затем сформировать из нее диаграмму либо линейный график. Сейчас детально останавливаться на этой теме не будем, но если она вас интересует, перейдите по ссылке ниже для прочтения другой моей статьи по этой теме.

Составление графика функции для работы с формулами в Excel

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

Как рассчитать проценты числа

Расчет процента числа — это то, с чем вы столкнетесь в повседневной жизни. Хорошим примером будет предмет для продажи, где скидка составляет 20% применяется к первоначальной цене $ 200. Сотрудник магазина должен знать, что такое 20% от 200 долларов. Затем они могли бы вычесть это число из первоначальной цены, чтобы предоставить цену со скидкой.

Это требует еще одного простого математического вычисления в Excel. Знак умножения (*) и знак процента (%) используются здесь. Вычислить 20% от суммы $ 200 , вы можете использовать либо = 20% * 20 или = 0,2 * 200 сделать расчет в Excel.

Использовать ссылки на ячейки, где 20% находится в клавише B4 и первоначальная цена $ 200 находится в клавише B5, вы можете использовать формулу = B4 * B5.

Проценты числа, показанные в Microsoft Excel

Результат тот же, используете ли вы 20%, 0.2 или отдельные ссылки на ячейки в вашей формуле.

20% от 200 долларов равно $ 40, как показано в ячейках От B6 до B8 над.

Читать еще:  Ошибка: отсутствует msvcp140.dll. Как исправить
Ссылка на основную публикацию
Adblock
detector