Excel имеет различные функции, включая функции расчета арккосинуса заданного значения, умножение двух матриц, оценки внутренней нормы доходности. Но большинство из нас (ну, и я в том числе) использует не более 5-6 формул для работы. И формула ЕСЛИ находится в этом списке. Так что, не повредит узнать еще несколько интересных вещей, которые можно сделать только с одной функцией ЕСЛИ.
- 1.Сумма альтернативных столбцов/строк
- 2.Подсчет количества повторений в листе А элементов из листа Б
- 3.Быстрый подсчет данных с помощью СЧЁТЕСЛИ и СУММЕСЛИ
- 4.Поиск второго, третьего … n-ного вхождения элемента в списке EXCEL
- 5.Сокращаем количество вложений функции ЕСЛИ
- Вам также могут быть интересны следующие статьи
1.Сумма альтернативных столбцов/строк
Случается такое, когда нам необходимо суммировать каждую вторую строку столбца (не спрашивайте меня зачем, такое случается и не редко). Конечно, мы могли бы суммировать каждую ячейку по отдельности, но это не наш вариант. Вместо этого мы устроим себе кофе-брейк, в то время как Excelсделает за нас всю грязную работу. Для этого нам понадобится альтернативный столбец и функция СУММЕСЛИ, которая суммирует ячейки, заданные определенным условием. В нашем случае критерием будет «если элемент находится в нечетных строках».
Все что нам нужно сделать, это добавить дополнительный столбец и заполнить его единицами и нулями (введите 1 и 0 в две строки, выделите обе ячейки и перетащите до конца таблицы). Теперь мы можем использовать наш столбец для определения условия с помощью функции =СУММЕСЛИ(диапазон условия; 1; диапазон суммирования).
2.Подсчет количества повторений в листе А элементов из листа Б
Часто, когда вы работаете с несколькими листами, необходимо подсчитать количество повторений какого-либо элемента на другом листе. Это можно реализовать с помощью старой доброй СЧЕТЕСЛИ, которая считает количество ячеек, удовлетворяющих заданному условию.
В примере выше я использовал функцию СЧЁТЕСЛИ, чтобы посчитать, сколько клиентов находиться в определенном городе (где список с клиентами находится на Листе 2, список с городами на Листе 1). Формула выглядит следующим образом =СЧЁТЕСЛИ(диапазон условий; условие). К примеру, формула СЧЁТЕСЛИ($E$6:$E$16; «Волгоград») скажет мне, сколько клиентов находиться в Волгограде.
3.Быстрый подсчет данных с помощью СЧЁТЕСЛИ и СУММЕСЛИ
Теперь, когда мы выяснили, как пользоваться формулами СЧЁТЕСЛИ и СУММЕСЛИ, может использовать их вместе.
Например, мы можем выяснить средний объем дохода от клиента, приходящегося на каждый город путем деления суммы дохода города на количество клиентов. Это быстрый сводный анализ без использования инструмента построения сводных таблиц.
4.Поиск второго, третьего … n-ного вхождения элемента в списке EXCEL
Часто случается так, что мы работаем со списком данных, имеющих много дубликатов, например, в список телефонов клиента со временем могут добавляться новые данные. Получить второе, третье … десятое вхождение может быть не простым. С помощью комбинаций СЧЁТЕСЛИ и ВПР мы можем найти любое появление в списке.
Для начала добавим дополнительную колонку в таблицу с данными о клиентах и пропишем в ячейки формулу, которая к имени клиента добавляет порядковый номер вхождения имени в списке. Таким образом, при первом появлении в списке, формула выдаст Имя1, при втором Имя2 и так далее.
Далее, при поиске информации о Кристине Агилере, мы будем пользоваться измененными данными клиентов. К примеру, формула ВПР(“Кристина Агилера3«, диапазон поиска, 2, ЛОЖЬ) выдаст нам третий номер телефона Кристины Агилеры. Обратите внимание, что последний аргумент формулы ВПР будет ЛОЖЬ, так как наш список не отсортирован по алфавиту и нам требуется точное совпадение значений поиска.
5.Сокращаем количество вложений функции ЕСЛИ
В более ранних версиях Excel, допускалось всего 7 уровней вхождения функции ЕСЛИ. Начиная с Excel 2007 это ограничение снято. К счастью, большинство из нас никогда не опускалось ниже 3-го или 4-го уровня. Но зачем писать такие многоуровневые формулы, если можно воспользоваться функцией ВЫБОР, которая выбирает значение или действие из списка значений по номеру индекса. Синтаксис функции выглядит следующим образом ВЫБОР(номер_индекса; значение_при_индексе_равном_1; значение_при_индексе_равном_2; значение_при_индексе_равном_2…). Например, следующая функция, ВЫБОР(3; «Отлично»; «Хорошо»; «Плохо»), вернет «Плохо», если ее вбить в ячейку. Есть одно ограничение, значение номера индекса должно быть задано в числовом формате, поэтому потребуется немного творчества (как в следующем примере), когда значение индекса нельзя задать явно:
Как я перевел значение буквы в число в функции ВЫБОР(), ну пускай это будет вашим домашним заданием)
