Microsoft Excel – Продвинутый пользователь Excel

Инструкция по оплате для студентов из России

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

Существует два типа ссылок в Excel на ячейки: относительные и абсолютные. Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении в другие ячейки. Относительные ссылки меняются при копировании формулы в другую ячейку. Тогда как, абсолютные ссылки остаются неизменными независимо от того, куда они копируются

Функция СЧЁТЕСЛИ позволят посчитать количество значений в диапазоне на основании ОДНОГО критерия, СЧЁТЕСЛИМН позволят посчитать количество значений в диапазоне на основании НЕСКОЛЬКИХ критериев

Рассмотрим возможности символов подстановки, которые позволяют управлять критериями поиска

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

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

Функция СУММЕСЛИ позволят суммировать значения в диапазоне на основании ОДНОГО критерия, СУММЕСЛИМН позволят суммировать значения в диапазоне на основании НЕСКОЛЬКИХ критериев.

Функция ЕСЛИ в Excel - это логический оператор, который позволяет на уровне рабочего листа принимать решения

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

Вместо скучной текстовой информации ДА/НЕТ/ВНИМАНИЕ, воспользуемся символами эмодзи и встроим их в нашу формулу ЕСЛИ

Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.

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

А еще я расскажу вам немного про индекс BigMac

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

Функция ИНДЕКС Excel возвращает конкретное значение ячейки в массиве на основе заданной строки и столбца

Функция ПОИСКПОЗ возвращает позицию конкретного значения в одномерном вертикальном или горизонтальном массиве

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

Формула ИНДЕКС и ПОИСКПОЗ редко используются сами по себе. Чаще всего вы можете встретить их в рабочих файлах, как комбинацию вложенных друг в друга формул. Такая комбинация позволяет нам создавать динамические формулы, которые могут возвращать необходимое значение из массива на основе с заданной строки и заданного столбца

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

Функция СУММПРОИЗВ возвращает сумму произведений из соответствующих диапазонов или массивов

C помощью функций НАИБОЛЬШИЙ, НАИМЕНЬШИЙ и РАНГ Вы можете отсортировать или ранжировать значение из вашего диапазона.

Формула массива — это формула, которая может выполнять несколько вычислений с одним или несколькими элементами массива. Формулы массива могут возвращать как несколько результатов (Формула массива с несколькими ячейками), так и один (Формула массива с одной ячейкой).

Формулы массивов невероятно мощный инструмент, однако с ними бывает очень сложно работать. Вот несколько ЗА и ПРОТИВ использования формул массивов

Константы массивов создаются путём ручного ввода элементов массива напрямую в панель формул и ручного ввода фигурных скобок {}

Константы массивов могут содержать числовые значения, текст (окруженный кавычками “”), логические значения (ИСТИНА, ЛОЖЬ), ошибки (#Н/Д) и могут быть использованы как часть формул массивов.

Именованные константы массивов задаются точно также, как обычные именованные диапазоны - с помощью Диспетчера имен, что упрощает их использование

Функция ТРАНСП Excel позволяет изменять ориентацию таблицы так, чтобы строки стали столбцами, а столбцы строками. Функция преобразует вертикальный диапазон ячеек в горизонтальный и наоборот

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

С помощью формулы массивов вернем массив из трех наибольших значений диапазона

Создадим формулу массивов, которая позволит просуммировать количество символов в ячейках целого диапазона

В Excel не существует готовой формулы МАКСЕСЛИ, однако существуют формулы массивов, которые помогут создать необходимые функции

Я возьму функцию МАКСЕСЛИ, которую мы создали в предыдущем уроке и немного усовершенствуем её. А именно, вместо того, чтобы возвращать максимальное значение основываясь на одном критерии - в нашем случае это месяц - Я хочу модифицировать данную функцию таким образом, чтобы у меня появился еще один критерий. Для нас таким критерием будет выступать фамилия работника.