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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В Excel существует два способа создания макросов: 1) Это активизировать средство записи макросов и записать свои действия 2) Либо ввести текст программы непосредственно в модуль VBA. Мы поговорим о первом подходе.

И в этом уроке я расскажу вам как сохранять файлы, которые содержат в VBA код

С помощью редактора Visual Basic Вы можете создавать новые макросы, редактировать уже имеющиеся или удалять их

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

Метод - это действие или активность, которую объект может выполнять