Билл Джелен. Динамические массивы в Excel

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

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

Скачать заметку в формате Word либо pdf, примеры в формате Excel

Содержание

Начало работы

Введение

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

О динамических массивах было объявлено 24 сентября 2018 года, но даже в MS Excel 2019 они пока не представлены. Динамические массивы доступны лишь в Office 365. Я думаю, что парадигма покупки пожизненной лицензии на Office любые три либо 6 лет устарела, и рекомендую перебегать на подписку.

Как организована эта книжка. В главе 1 вы узнаете о концепции формулы, распространяющейся на примыкающие ячейки. Вы увидите, как непустая ячейка может перекрыть разлив массива, и как это поправить. Вы услышите о неявном пересечении и о том, как нотация @ может решить эту делему. Главы 2–8 посвящены каждой из 6 новейших функций и их композициям. Глава 9 базирована на потрясающем видео Джо Макдэйда. Из данной главы вы узнаете определения, которые употребляет команда Excel Calc: Подъем, Трансляция, Попарный подъем, Усечение массива и Неявное пересечение. В главе 10 рассматриваются два метода, при помощи которых динамические массивы делают новейшую функцию ПРОСМОТРX наиболее сильной. В 11-й – приведены 24 примера объединения динамических массивов с рядом обыденных функций Excel. В 12-й главе показано, как динамические массивы могут работать с функциями кубов.

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

Глава 1. Начало работы

Формулы сейчас могу разливаться

В данной главе рассматривается новенькая формула =A2:A20, ошибка #ПЕРЕНОС! и новейший неявный оператор пересечения @.

Начнем с базисной формулы массива. Перейдите в ячейку Е3. Наберите =A2:C10. В наиболее ранешних версиях Excel для вас пришлось бы включить этот спектр в качестве аргумента какой-либо функции, либо употреблять формулу массива, сразу нажав Ctrl+Shift+Enter.

Рис. 1. Формула показывает на спектр ячеек

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

Рис. 2. Одна формула возвратила огромное количество значений

Ячейка E4 содержит текст Central, и, хотя строчка формул указывает формулу для данной ячейки, она отображается сероватым цветом. Давайте проверим при помощи VBA, что содержится в ячейках Е3 и Е4?

Рис. 3. VBA подтверждает, что в ячейке Е4 не формула

VBA указывает, что в ячейке Е3 – формула, а в Е4 – нет. Также в Excel можно ввести формулу =ЕФОРМУЛА(E4). Она возвратит ЛОЖЬ. И еще одна проверка. Выберете спектр D1:H20, и пройдите по меню Основная –> Отыскать и поменять –> Формулы. Будет выделена лишь ячейка E3.

Один из первых вопросцев на YouTube в ответ на мои 1-ые видео с динамическими массивами был: можно ли вы копировать и вставлять значения? Да! Изберите спектр E3:G11, нажмите Ctrl+C, кликните правой клавишей мыши на избранную новейшую ячейку и изберите Особая вставка –> Значения.

Что происходит, если формула не может пролиться?

Что произойдет, если ячейка, куда должен разлиться спектр, будет занята?

Рис. 4. Как Excel управится с занятой ячейкой?

Excel возвратит ошибку #ПЕРЕНОС! Excel докладывает, что не может возвратить массив полностью. Потому не возвратит ни 1-го результата. Если избрать контекстное меню слева от ячейки с ошибкой, можно выделить мешающие ячейки. Может быть, их получится перенести в другое пространство листа.

Рис. 5. Ошибка #ПЕРЕНОС! и ее контекстное меню

Как вы очистите ячейки, мешающие размещению массива, он автоматом разольется.

Существует несколько типов ошибки #ПЕРЕНОС! Та, что выше, именуется Спектр для переноса данных не пустой. Остальные ошибки:

Неопределенный размер. Вы не сможете употреблять волатильные функции, к примеру, СЛУЧМЕЖДУ(), в качестве аргумента функции ПОСЛЕДОВ().
Выходит за границы листа. Вы не сможете ввести функцию =СОРТ(C:C) в ячейке E2.
Табличная формула. Вы не сможете употреблять функции динамического массива снутри Таблицы.
Не хватает памяти. Для вас следует ссылаться на спектр наименьшего размера.
Разлив в объединенные ячейки. Динамический массив не может разливаться в объединенную ячейку.
Неопознанная ошибка. Excel не может распознать ошибку.

Интересно почитать: Перевернуть таблицу в excel

Если ваша формула показывает на Таблицу, динамический массив будет расширяться при добавлении новейших строк в Таблицу

Рис. 6. Динамический массив «выслеживает» Таблицу

Сравните с рис. 2. Вы преобразовали спектр А1:С19 в Таблицу (Ctrl+T). Формула в ячейке Е3 поменялась на =Таблица1. Сейчас, если вы добавите еще одну строчку в Таблицу (А11:С11), формула в ячейке Е3 не поменяется, а динамический массив автоматом расширится очередной строчкой (Е12:G12).

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

Внедрение новейшего типа ссылок на массив: E3#

Как вы возжелаете сослаться на массив E3:G12, но не понимаете, какого он размера, добавьте оператор разлитого спектра (#) опосля ячейки, содержащей формулу массива.

К примеру, =E3 возвратит East, =E3# возвратит весь массив, формула которого хранится в E3. Неофициально это именуется ссылочной нотацией массива. Таковая нотация поддерживается лишь при ссылке снутри одной книжки.

Что такое неявное пересечение

Если вы введете =@C2:C11 в хоть какой ячейке в строчках со 2 по 10, формула возвратит значение из столбца С той строчки, в которой вы ввели формулу. Символ @ известен как неявный оператор пересечения.

Динамическая диаграмма в Excel

Может быть ли в Excel сделать динамическую диаграмму? В определенном смысле да. Если под динамическим осознавать, автоматическое дополнение диаграммы новенькими данными и выделение наибольшего и малого значения. Динамическая диаграмма в Excel – вероятна.

Начальные данные

Итак, пусть будут такие данные:

Есть некоторые значения, характеризующие определенный месяц. Это могут быть реализации, создание продуктов, да что угодно. Нужно на базе этих данных выстроить гистограмму с группировкой. Если созодать как обычно, другими словами выделить весь спектр, либо просто встав на ячейке с данными избрать «Вставка» — «Гистограмма» — «Гистограмма с группировкой». Диаграмма будет вставлена, но это будет диаграмма на базе данных. Диаграммы такового типа не поддерживают 1-ый нужный элемент динамичности, а конкретно динамическое добавление колонок при внесении новейших строк в таблицу.

Динамическое добавление колонок

Лишь диаграммы, основанные на таблицах Excel, поддерживают динамическое изменение количества строк. Для того что бы из спектра ячеек В2:С8 создать таблицу, так же довольно встать на хоть какой ячейки и избрать меню «Вставка» — «Таблица», либо надавить комбинацию кнопок Ctrl+T.

Преобразование диапазона в таблицу Excel

Раскроется диалоговое окно прибавления таблицы. В поле для ввода, диалогового окна будет указан спектр ячеек, которые будут составлять таблицу. Убедимся, что в таблицу входят все ячейки и нажмем клавишу «ОК». Заместо спектра ячеек, обязана будет показаться таблица.

Встанем в всякую ячейку таблицы и сделаем на ее базе гистограмму.

Пока что всё как обычно. Где же динамичность? По сути она уже есть. Если перейти в ячейку В9, которая не является частью таблицы и ввести данные, в нашем случае это будет «Июль», то благодаря свойству таблиц, новенькая строчка будет включена в состав таблицы, произойдет авто расширение таблицы. Потому что диаграмма связанна не с спектром ячеек, а с таблицей то произойдет добавление новейшего столбца в диаграмму. Вот так:

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

Выделение столбцов, максимальное-минимальное

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

Во первых, как найти наибольший либо малый столбец? Лишь зрительно, для того что бы в данных столбец с наибольшим значением был выделен остальным цветом, добавим очередной столбец в таблицу. Назовем столбец «Максимум» и запишем в нем такую формулу:

Да, не забываем про изюминка таблиц к авто расширению. Для того что бы добавить столбец в таблицу довольно в примыкающем с крайним столбцом таблицы ввести значение, так в нашем случае введя в поле D3 значение «Максимум» мы добавим столбец в таблицу. В первой строке таблицы введем формулу и при нажатии на всплывающее меню распространим действие формулы на весь столбец (но можно и обычным образом растянуть формулу на весь столбец).

Интересно почитать: Зеркальные поля в excel

Направьте внимание, что в формуле употребляются особые табличные ссылки, а не обычные вида «столбец-строка». Если табличная ссылка начинается с знака «@» — это значит текущую строчку, столбец с обозначенным именованием. Если имя столбца указанно просто в квадратных скобках – это значит ссылку на столбец полностью. Таковым образом, наша формула инспектирует если значение в текущей строке, столбца «Показатель» равно наибольшему значению столбца «Показатель», то выводится значение текущей строчки столбца «Показатель», если значение не равно, выводится «#Н/Д» — нет данных. Особое значение «#Н/Д» используем, что бы все столбцы, где значение не наибольшее отсутствовали в диаграмме. Опосля ввода формулы наша таблица и диаграмма будут иметь вид:

Малое значение

Эм, пока незначительно удивительно смотрится, но это не жутко. По сути добавив новейший столбец в таблицу, мы добавили и новейший столбец в диаграмму, что разумно. У нас в таблице два схожих наибольших значения, потому и два столбца красноватого цвета. Индивидуальностью гистограмм является внедрение 1-го цвета для значений одной группы (1-го столбца), иной столбец иной цвет. Если нужно поменять цвет, то лучше это созодать на этом шаге. Как поменять цвет и настроить наружный вид диаграммы не относится к данной статье, потому разглядывать этот нюанс не будем.

Сейчас добавим очередной столбец в таблицу, назвав его «Минимум» и введя в него формулу:

Формула подобна предшествующей, за тем исключением что заместо функции «МАКС» используем «МИН», ведь проверяем мы на малое значение. В итоге получим таковой вид таблицы и диаграммы.

Да у элемента «Январь» в диаграмме возник очередной столбец. В нашей диаграмме любой столбец, прижат впритирку к примыкающему, на любой элемент по три столбца. Просто для частей значения, которых не являются ни наибольшим, ни наименьшим, нет данных, а означает, столбцы в диаграмме отсутствуют. То, что столбца конкретно три можно убедиться на примере зеленоватого малого показателя, меж голубым и зеленоватым пиком есть просвет. Тогда как в наибольших элементах меж голубым и красноватым промежутка нет. Всё поэтому что для элемента «Январь» значение «Максимум» отсутствует, те равно «#Н/Д».

Характеристики ряда диаграммы

А на данный момент самое увлекательное. Выделим нажатием левой клавишей мыши голубые столбцы в диаграмме. Либо еще резвее сходу нажатием правой клавиши мыши выделим столбцы и вызовем контекстное меню, в котором выберем пункт «Формат ряда данных».

В открывшемся диалоговом окне, в разделе «Характеристики ряда» 1-ый ползунок именуется «Перекрытие рядов» по дефлоту его значение – 0%. Переместим его на право к надписи «С перекрытием», либо введем значение 100 в поле для ввода. Вот так:

Нажмем клавишу «Закрыть» что бы избавиться от диалогового окна. Почему закрыть, а не обычное «ОК», да поэтому что конфигурации вносимые в этом диалоговом окне здесь же отражаются в диаграмме, и если у вас из под диалогового окна была видна диаграмма, то вы уже узрели что заместо 3-х столбцов остался лишь один. Два остальных оказались просто перекрыты. Любая последующая колонка перекрывает предшествующую, где одна колонка остался голубий столбец. Где были голубий и красноватый, последующий красноватый столбец перекрыл голубий, а потому что зеленоватого не было, в диаграмме виден красноватый столбец. Для малого значения выходило, что голубий должен перекрываться последующим, но в последующем красноватом нет значения, он пустой и невидимый, зато есть 3-ий зеленоватый он то и перекрывает 1-ый. Так наша диаграмма приняла прекрасный законченный вид:

Если на данный момент поиграть значениями столбца «Показатель» в таблице то можно узреть что значения в диаграмме тоже будут изменяться, при всем этом если значение окажется наименьшим либо наибольшим, то подходящим цветом будет выделен нужный столбец.

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

Динамические таблицы в Excel

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

Динамические таблицы в Excel

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

Интересно почитать: Как в эксель сделать автозамену

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

На самом деле, есть два главных достоинства динамических таблиц:

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

Как сделать динамические таблицы в Excel?

Существует два главных метода использования динамических таблиц в Excel: 1) Внедрение ТАБЛИЦ и 2) Внедрение функции OFFSET.

# 1 — Внедрение таблиц для сотворения динамических таблиц в Excel

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

пример

У нас есть последующие данные,

Если мы сделаем сводную таблицу с сиим обычным спектром данных от A1 до E6, то, если мы вставим данные в строчку 7, они не отразятся в сводной таблице.

Итак, поначалу мы создадим динамический спектр.

# 1 — Изберите данные, к примеру A1: E6.

# 2 — На вкладке «Вставка» щелкните «Таблицы» в разделе таблиц.

# 3 — Покажется диалоговое окно.

Потому что у наших данных есть заглавия, не забудьте установить флаг «Моя таблица имеет заглавия» и надавить ОК.

# 4 — Наш динамический спектр сотворен.

# 5 — Изберите данные и на вкладке «Вставка» в разделе «Таблицы Excel» щелкните сводные таблицы.

# 6 — Так как мы сделали таблицу, она воспринимает спектр, как Таблица 2. Нажмите OK и в сводных таблицах перетащите продукт в строчки и реализации в значения.

# 7 — Сейчас в Sheet, где у нас есть наша таблица, вставьте Another Data в 7 th

В сводной таблице обновите сводную таблицу.

В нашей динамической сводной таблице автоматом обновляются данные о продукте 6 в сводной таблице.

# 2 — Внедрение функции OFFSET для сотворения динамической таблицы в Excel

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

пример

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

Изберите данные и дайте им имя

Сейчас, когда я обращаюсь к прайс-листу набора данных, он переводит меня к данным в спектре B2: C7, в котором есть мой прайс-лист. Но если я обновлю другую строчку данных, она все равно приведет меня к спектру B2: C7, поэтому что наш перечень статичен.

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

# 1 — На вкладке «Формулы» в «Определенном спектре» нажмите «Определенное имя», и покажется диалоговое окно.

# 2 — В поле Имя введите хоть какое имя, я буду употреблять PriceA. Область деяния — это текущая книжка, и в истинное время она ссылается на текущую избранную ячейку, другими словами B2.

В Ссылке написать последующую формулу:

= смещение (Sheet2! $ B $ 2,1,0, counta (Sheet2! $ B: $ B) -1,2)

= смещение (

# 3 — Сейчас изберите исходную ячейку, другими словами B2.

# 4 — Сейчас нам необходимо ввести 1,0, потому что он посчитает, сколько строк либо столбцов осталось.

# 5 — Сейчас нам необходимо, чтоб он подсчитал все данные в столбце B и употреблял это как количество строк, потому используйте функцию COUNTA и изберите столбец B.

# 6 — Так как мы не желаем, чтоб подсчитывалась 1-ая строчка, которая является заголовком продукта, потому (-) 1 от нее.

# 7 — Сейчас количество столбцов постоянно будет равно двум, потому введите 2 и нажмите OK.

# 8 — Этот спектр данных не будет отображаться по дефлоту, потому, чтоб узреть это, нам необходимо надавить на Диспетчер имен на вкладке Формула и избрать Продукт,