​​Массивы функций: Синтаксис

Формулы массива можно рассматривать, как комбинацию массивов констант, оператора массива и диапазона массива. Таким образом формула массива использует массивы, как часть аргументов.

При вводе формул массива обязательно нажимать CTRL + SHIFT + ENTER, а не просто ENTER, как при обычных формулах.

• Массив констант — это набор статических значений, которые не ссылаются на другие ячейки или диапазоны, поэтому они всегда будут одинаковыми независимо от изменений происходящих на листе;
• Оператор массива сообщает формуле, какую операцию необходимо совершить над массивами. К тому же, вы можете использовать операторы И и ИЛИ;
• Диапазон массива вводится точно также, как и в обычных формулах (например, A1:A10).
👉 @Excel_lifehack

Сводную можно построить так, что в строках/столбцах будет > 1 поля. Тогда может потребоваться объединение ячеек.
Простая команда не сработает, но аналог есть в Параметрах сводной
👉 @Excel_lifehack

Массивы функций: Горизонтальный массив констант

Горизонтальный массив констант вводится, как последовательность чисел, разделенных точкой с запятой и заключенных в фигурные скобки. Например: {1;2;3;4;5}.

Горизонтальные массивы могут быть использованы в качестве входных данных для формулы массива. Они также могут быть введены в таблицу, как показано ниже.
👉 @Excel_lifehack

Когда используете спец.вставку,то можно вкл. опцию "пропускать пустые ячейки". Пригодится при слиянии столбцов в единый диапазон (если в каждой строке заполнен только 1 столбец)
👉 @Excel_lifehack

Массивы функций: Вертикальный массив констант

В отличие от горизонтального, в вертикальном массиве констант значения разделяются двоеточием (:) и также заключаются в фигурные скобки. Например: {1:2:3:4:5}.
👉 @Excel_lifehack

Подсчитать число ячеек с текстом/числами внутри диапазона поможет формула массива с использование специальных функций проверки типа данных в ячейке. Вводим через Ctrl+Shift+Enter
👉 @Excel_lifehack

Массивы функций: Оператор массива И

Оператор И (*) возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример на картинке показывает его использование между массивами.
👉 @Excel_lifehack

Нас часто спрашивают: "Как закрепить нижнюю строку?". Закрепление областей работает только с верхними. Но можно имитировать закрепление нижней строки командой Разделить
👉 @Excel_lifehack

​​Массивы функций: Оператор массива ИЛИ

Оператор ИЛИ (+) возвращает значение ИСТИНА, если хотя бы одно из условий выражения возвращает значение ИСТИНА.
Пример на картинке показывает его использование между массивами.
👉 @Excel_lifehack

Чтобы перемешать строки в таблице/столбце случайным образом, можно просто создать рядом дополнительный столбец с функцией СЛУЧМЕЖДУ. А уже по нему можно делать сортировку

👉 @Excel_lifehack

Сортировка с помощью формулы массива

Предположим, у вас есть набор данных в ячейках D2:D10, и вы хотите отсортировать его в порядке возрастания.
Для этого понадобится функция НАИМЕНЬШИЙ(), а также диапазон, в котором мы будем производить вычисления.

Обычная функция НАИМЕНЬШИЙ для одной ячейки выглядит так =НАИМЕНЬШИЙ(D2:D10;1).
Необходимо скопировать эту функцию во все остальные ячейки и внести изменения во второй аргумент, чтобы получить отсортированный список.

Для начала выделим диапазон, в котором хотим увидеть список, затем вводим формулу в первую ячейку и жмем CTRL + SHIFT + ENTER.
Формула будет скопирована на весь диапазон, результатом станет отсортированный список.

👉 @Excel_lifehack

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

👉 @Excel_lifehack

Поиск уникального значения

Предположим, мы хотим выяснить имя менеджера с наибольшими продажами.
Если бы мы использовали обычные формулы, понадобилось столько же строк, сколько и менеджеров. Однако мы можем сделать тоже самое в одну формулу массива:

=СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0)

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

👉 @Excel_lifehack

Для сортировки ячееек в диапазоне по числовому формату, нужно создать отдельный столбец с функцией ЯЧЕЙКА. Она умеет определять формат ячейки (числовой, текстовый и т.д.)

👉 @Excel_lifehack

Консолидация данных по более чем одному условию

Мы также можем использовать формулу массива для поиска суммы продаж менеджера с максимальными продажами.

Функция ЕСЛИ возвращает массив отдельных сумм продаж менеджера совпадающего с менеджером с максимальными продажами, иначе 0.
Затем мы используем функцию СУММ для суммирования всех этих значений массива.

👉 @Excel_lifehack

Иногда при построении диаграммы годы не откладываются на оси X, а принимаются за ряд данных (если годы - числовые значения). Сделайте их текстом или уберите заголовок из столбца с ними

👉 @Excel_lifehack

Ошибки в формулах: виды и способы устранить

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

1) Ошибка ###############################
Если ячейка вдруг целиком заполнилась символами решётки, то варианта всего два: либо значение ячейки не помещается в нее, либо введено отрицательное значение времени.
В первом случае достаточно расширить столбец или уменьшить шрифт, а во втором - исправить значение времени.

2) Ошибка ДЕЛ/0!
Такая ошибка возникает, если в формуле происходит деление на 0 или на пустую ячейку. Соответственно, исправив нулевой или пустой знаменатель, можно устранить ошибку.

👉 @Excel_lifehack

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

👉 @Excel_lifehack

Ошибки в формулах: виды и способы устранения

3) Ошибка Н/Д
Такая распространенная ошибка возникает, когда функция поиска данных не находит искомое значение в диапазоне. Функции поиска — это ВПР, ГПР, ПОИСКПОЗ, ПРОСМОТР.

Решается либо изменением поискового запроса, либо внесением в диапазон искомого значения. Однако, чаще всего эта ошибка вполне ожидаема и просто помогает проверить наличие того или иного значения в списке.

Многие предпочитают выводить вместо нее пустые значения или какой-то значимый текст с помощью функции ЕСЛИОШИБКА, например:
=ЕСЛИОШИБКА(ВПР(A1;B:C;2;0);"Отсутствует в справочнике")

4) Ошибка ИМЯ?
Возникает, когда в формуле используется неопознанное имя. Именем считается любой текст, не являющийся названием функции, ссылкой на ячейку/диапазон и не взятый в кавычки.

Например, в формуле =СЕГОДНЯ()+СЕГ-A4 слово СЕГ будет распознано, как имя. Способы решения:
• создать нужное имя в диспетчере имен;
• проверить правильность написания уже существующего имени;
• проверить, верно ли написаны функции рабочего листа (опечатки приведут к возникновению ошибки).

👉 @Excel_lifehack

Иногда текст в ячейках разбит на строки с помощью переноса строки (Alt+Enter). Если нужно разбить на отдельные столбцы - используйте Текст по столбцам. Ввод разделителя - Ctrl+J

👉 @Excel_lifehack

Ошибки в формулах: виды и способы устранения

5) Ошибка ССЫЛКА!
Данная ошибка возникает в случае, когда ячейка или диапазон, на который ссылается формула, был удален, перемещен или стал недоступным.

Чтобы исправить ошибку, нужно отменить удаление ячейки или заново сослаться на нужный диапазон. Другой вариант — использовать функцию ДВССЫЛ для тех ячеек, которые могут быть удалены.

Еще один способ возникновения - файлы, на которые есть ссылки были перемещены, удалены или переименованы.

6) Ошибка ЗНАЧ!
Возникает чаще всего тогда, когда в формуле использован неверный тип данных. Помните, что текст, число или дата - разные типы данных и обрабатываются по разному. Для исправления проверьте соответствуют ли все аргументы требуемым типам данных, если нет - укажите правильные типы.

👉 @Excel_lifehack