Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали

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

1.      Сумма альтернативных столбцов/строк

Случается такое, когда нам необходимо суммировать каждую вторую строку столбца (не спрашивайте меня зачем, такое случается и не редко). Конечно, мы могли бы суммировать каждую ячейку по отдельности, но это не наш вариант. Вместо этого мы устроим себе кофе-брейк, в то время как Excelсделает за нас всю грязную работу. Для этого нам понадобится альтернативный столбец и функция СУММЕСЛИ, которая суммирует ячейки, заданные определенным условием. В нашем случае критерием будет «если элемент находится в нечетных строках».

1-sum-alternative-rows-columns-excel - копия

Все что нам нужно сделать, это добавить дополнительный столбец и заполнить его единицами и нулями (введите 1 и 0 в две строки, выделите обе ячейки и перетащите до конца таблицы). Теперь мы можем использовать наш столбец для определения условия с помощью функции =СУММЕСЛИ(диапазон условия; 1; диапазон суммирования).

2.      Подсчет количества повторений в листе А элементов из листа Б

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

 2-using-countif-ms-excel

В примере выше я использовал функцию СЧЁТЕСЛИ, чтобы посчитать, сколько клиентов находиться в определенном городе (где список с клиентами находится на Листе 2, список с городами на Листе 1). Формула выглядит следующим образом =СЧЁТЕСЛИ(диапазон условий; условие). К примеру,  формула СЧЁТЕСЛИ($E$6:$E$16; “Волгоград”) скажет мне, сколько клиентов находиться в Волгограде.

3.      Быстрый подсчет данных с помощью СЧЁТЕСЛИ и СУММЕСЛИ

Теперь, когда мы выяснили, как пользоваться формулами СЧЁТЕСЛИ и СУММЕСЛИ, может использовать их вместе.

3-summarize-data-with-countif-sumif

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

4.      Поиск второго, третьего … n-ного вхождения элемента в списке EXCEL

Часто случается так, что мы работаем со списком данных, имеющих много дубликатов, например, в список телефонов клиента со временем могут добавляться новые данные. Получить второе, третье … десятое вхождение может быть не простым. С помощью комбинаций СЧЁТЕСЛИ и ВПР мы можем найти любое появление в списке.

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

4-find-second-occurance-using-vlookup

Далее, при поиске информации о Кристине Агилере, мы будем пользоваться измененными данными клиентов. К примеру, формула ВПР(“Кристина Агилера3”, диапазон поиска, 2, ЛОЖЬ) выдаст нам третий номер телефона Кристины Агилеры. Обратите внимание, что последний аргумент формулы ВПР будет ЛОЖЬ, так как наш список не  отсортирован по алфавиту и нам требуется точное совпадение значений поиска.

5.      Сокращаем количество вложений функции ЕСЛИ

В более ранних версиях Excel, допускалось всего 7 уровней вхождения функции ЕСЛИ. Начиная с Excel 2007 это ограничение снято. К счастью, большинство из нас никогда не опускалось ниже 3-го или 4-го уровня. Но зачем писать такие многоуровневые формулы, если можно воспользоваться функцией ВЫБОР, которая выбирает значение или действие из списка значений по номеру индекса. Синтаксис функции выглядит следующим образом ВЫБОР(номер_индекса; значение_при_индексе_равном_1; значение_при_индексе_равном_2; значение_при_индексе_равном_2…). Например, следующая функция, ВЫБОР(3; «Отлично»; «Хорошо»; «Плохо»), вернет «Плохо», если ее вбить в ячейку. Есть одно ограничение, значение номера индекса должно быть задано в числовом формате, поэтому потребуется немного творчества (как в следующем примере), когда значение индекса нельзя задать явно:

5-using-excel-choose-function

Как я перевел значение буквы в число в функции ВЫБОР(), ну пускай это будет вашим домашним заданием)



24 комментария

  1. К примеру 5:
    Функция КОДСИМВ() возвращает значения от букв A, B, C, D (англ.) — 65, 66, 67, 68 соответственно вычитая из этих значений 64, получаем 1, 2, 3, 4 для первого аргумента функции ВЫБОР().

  2. В 4-м пункте (дабы было удобнее пользоваться этим приемом) лучше в ячейках B6:B9 сделать пользовательский формат 0″-й», а в формуле ссылаться на соседнюю ячейку, то есть так (формула для ячейки C6):
    =ВПР($C$5&B6;$F$6:$G$24;2;ЛОЖЬ)

    в этом случае не придется вручную прописывать номер в формуле (в той формуле, которая сейчас показана на скриншоте)

  3. Pingback: Несколько условий ЕСЛИ в Excel | Exceltip

  4. а каким образом найти клиента в огромной базе по номеру телефона? Т.е. в таблице много параметров о клиентах и контактные данные в том числе (это как бы уникальный номер, только по нему можно найти нужного клиента). Мне это нужно, чтобы повторно не вносить одного и того же клиента, а такой риск у меня 95 к 100.

  5. Нашел интересное для себя. Буду пользоваться в дальнейшем.
    Автору респект и уважение.

    Жаль что старый excel не поддерживает большое количество строк — приходится переходить на последнюю версию офиса.

  6. Подскажите пожалуйста, как в формуле:
    =СУММЕСЛИ(C3:C14;»<1")-(СЧЁТЕСЛИ(C3:C14;"<1")-1)
    задать диапазон значений коэффициентов, отличных от 1 (менее 1, более 1, но менее 2).

  7. 4 пример. Не нумерует. а показывает только количество возникновения элемента, т.е. например, показывает везде «Джессика альба3». если придумаете ответ. пишите мне на электронку.

  8. Благодаря примеру 3, открыл для себя функцию СУММАЕСЛИ. Очень пригодилась, но мне этого мало.
    К примеру книга из 5-ти листов и более. Используя данную функцию в ячейке 1-го листа, хочется чтобы в ней суммировались значения со всех остальных листов, при тех же условиях, а не только с одного.
    Если такое возможно, подскажите кто знает.

  9. Добрый день! Пожалуйста! Подскажите! Как посчитать количество вхождений КС-1 в каждую из SP-1 и КС-5, количество Б-68 в ОГ-15 (разных данных в первом стоблце -1000, всего строк 10000, не создавать же 1000 разных =ВПР)
    SP-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    КС-2
    КС-1
    ———————-
    КС-5
    КС-2
    КС-1
    ———————-
    ОГ-15
    К-1 Б-282
    Б-68
    Б-68

  10. Не получается легко заполнить 01010 в первом примере. Ексель либо начинает считать в минус либо 0,666 строчит в столбик. Что не так?

  11. Здравствуйте. Есть таблица для снятия остатков по бару. Есть в таблице Кола 0.5 (Продаётся не только бутылками, но и идёт в коктейли) При этом при снятии остатков предположим от 29.15-2.250 то считает единицу как 1, а необходимо чтобы считало 1 как 0.5, т.е. в данном случае 29.150-2.250 должно получаться не 26.9, а 27.4 это возможно сделать или просто умножать в двое приход и расход?
    Благодарю, простите, если не правильно объяснил =)

  12. Здравствуйте! Подскажите, пожалуйста, как можно прописать формулу в файле во вложении.
    Строка стекло столбец АС.
    Скорее всего надо использовать формулу ЕСЛИ.
    Где строка количество: Если ДО=1, то ДГ должно быть=0 (чтобы не менять постоянно количество вручную). В строчках с 4 по 16 идет ссылка на количество ДГ, соответственно в этих строчках должно быть 0. Как мне можно это прописать, помогите, пожалуйста!

    Файл:  .xlsx

  13. Подскажите пожалуйста! На основной таблице есть порядковый номер№ и название предприятия. Я работаю со многими листами и приходится каждый раз писать, или обращаться с помощи формулы =Лист! (). Как сделать так чтобы при наборе порядкогого номера или еще чего то сразу выходило название предприятия. Есть ли такая возможность? Помогите пожалуйста!

  14. Здравствуйте!
    Помогите посчитать KPI исходя из выдачи в год и наличия на складах. Попыталась сама создать формулу, но опыта в этом деле нет, и не могу правильно сформировать её. Мне нужно так, к примеру: «выдача в год» делённое на 24 (половина месяца) меньше или равно «наличие на складах», где выдача и наличие в числовых измерениях. Заранее спасибо.

Добавить комментарий для галя Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *