Сентябрь 2020

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

ПРОСМОТРX – первая новая функция после появления динамических массивов

Функции ПРОСМОТРX и ПОИСКПОЗX дебютировали для Office 365 в августе 2019 года. Они были разработаны для объединения возможностей ВПР, ИНДЕКС, ПОИСКПОЗ и динамических массивов. Ранее я писал о двенадцати удивительных преимуществах ПРОСМОТРX.[1] Здесь расскажу лишь о двух из них. Но сначала введение для тех, кто еще не знаком с ПРОСМОТРX.

В ячейке D4 вы хотите найти категорию (Category) для элемента W25-6. До появления ПРОСМОТРX, вы могли это сделать с помощью =ВПР(A4;F6:I17;4;ЛОЖЬ) или комбинации функций =ИНДЕКС(I7:I17;ПОИСКПОЗ(A4;F7:F17;0)). С ПРОСМОТРX сделать это еще проще. Вы ищете значение ячейки A4 в диапазоне F$7:F$17, и хотите вернуть соответствующее значение из I$7:I$17. Нет необходимости в аргументе ЛОЖЬ функции ВПР или 0 функции ИНДЕКС, потому что ПРОСМОТРX по умолчанию ищет точное совпадение:

Рис. 68. Простой пример ПРОСМОТРX

Подробнее »Функция ПРОСМОТРX и динамические массивы в Excel

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Почему формулы массива (Ctrl+Shift+Enter) были так сложны для понимания?

Если вы когда-либо вводили Ctrl+Shift+Enter, эта глава прольет свет, почему формулы массива были столь трудными в использовании в предыдущих версиях Excel. Для начала несколько вопросов о работе прежних версий Excel. Я не ожидаю, что все ваши ответы будут правильными. Попробуйте угадать. Далее по ходу главы я внесу ясность в эти вопросы.

Вопрос 1. Если в ячейку D8 вы введете =B4:B14, каков будет результат?

• 2047, сумма значений B4:B14.
• 1, значение первой ячейки в диапазоне.
• 16, значение в той же строке диапазона.
• Ошибка #ЗНАЧ! синтаксис формулы неверный

Рис. 44. Что вы получите, если в старом Excel в одной ячейке попытаться вернуть много значений?

Подробнее »Почему формулы массива (Ctrl+Shift+Enter) такие сложные: неявное пересечение

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Функция СЛМАССИВ генерирует массив случайных чисел. Ее синтаксис:

Рис. 40. Синтаксис функции СЛМАССИВ()

Подробнее »Функция СЛМАССИВ динамических массивов в Excel

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Генерация диапазона последовательных чисел

У меня есть классный трюк.[1] На семинарах Power Excel я показываю, как можно получить последовательность натуральных чисел. Вводите в ячейку число 1, выделяете ячейку, нажимаете Ctrl, кликаете на маркер в правом нижнем углу, и протаскиваете его вниз. Excel заполняет ячейки числами 2, 3, 4, … Когда я впервые увидел функцию ПОСЛЕД, я подумал, что она была создана для тех, кто не знаком с этим трюком. Но, как вы увидите ниже и в главе 11, функция ПОСЛЕД является ключом к превращению многих других функций Excel в функции массива.

Функция ПОСЛЕД() генерирует массив чисел. Ее синтаксис

Рис. 34. Синтаксис функция ПОСЛЕД()

Подробнее »Функция ПОСЛЕД динамических массивов в Excel

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Функция УНИК() является частью коллекции формул динамического массива. Она возвращает неповторяющиеся значения массива. Синтаксис функции УНИК():

Рис. 27. Синтаксис функции УНИК()

Подробнее »Функция УНИК динамических массивов в Excel

Это перевод четвертой главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Фильтрация по одному условию

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

Рис. 20. Исходные данные

Подробнее »Функция ФИЛЬТР динамических массивов в Excel

Это перевод третьей главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Сортировка по чему-то, чего нет в результатах

Синтаксис функции СОРТПО:

Рис. 17. Можно сортировать по чему-то еще

Подробнее »Функция СОРТПО динамических массивов в Excel

Это перевод второй главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Рис. 8. Исходные данные

Подробнее »Функция СОРТ динамических массивов в Excel

Незаметно для меня Microsoft совершил прорыв, представив в сентябре 2018 новые возможности – динамические массивы. Ниже – перевод книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. Книги серии Straight to the Point предназначены для глубокого освещения одного аспекта Excel.

Билл Джелен – основатель сайта MrExcel.com и автор множества книг о Excel. Сайт бесплатно отвечает на более чем 30 000 вопросов в год. В моем блоге представлены три книги Джелена:

• Сводные таблицы в Microsoft Excel 2013,
• Всё о ВПР: от первого применения до экспертного уровня,
• Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Подробнее »Билл Джелен. Динамические массивы в Excel