Excel объединить два массива в один
What kind of operations can I perform on arr1 and arr2 and assign the result to arr3 getting something like that:
Hint (due to comment): "1) the elements in arr1 are names and in arr2 are values, the final elements in arr3 are actually name-value pairs, so as long as they as paired I won't care if they are not in order."
Two questions: (1) Is the order of elements in the merged array important? (2) Do you want to eliminate dupes if the same value appears in both arrays?
1) the elements in arr1 are names and in arr2 are values the final elements in arr3 are actually name value pairs so as long as they as paired I wont care if they are not in order. Hope that answers your question. 2) I think point 1 answered this, I will be taking care of dupe names eleswhere.
17 Answers 17
For in case, the arrays contain a comma, can use Split(Join(arr1, Chr(1)) & Chr(1) & Join(arr2, Chr(1)), Chr(1))
Fyi Posted an extension to your fine solution, using the new ArrayToText() function and allowing to return numeric values as further benefit. @user3286479
Unfortunately, the Array type in VB6 didn't have all that many razzmatazz features. You are pretty much going to have to just iterate through the arrays and insert them manually into the third
Assuming both arrays are of the same length
Updated: Fixed the code. Sorry about the previous buggy version. Took me a few minutes to get access to a VB6 compiler to check it.
Summing the UBounds gives a size off-by-one, and the write index into the output array should be stepped separately from read index into the source arrays. Let this be a lesson in how annoying VBA arrays can be to work with!
Just to expound on annoying VBA arrays. especially when combining Excel and VBA, the main thing you need to know is that arrays can have arbitrary lower bounds. If you don't specifiy one, then the LB is set by the Option Base setting. But arrays created with Array() and ParamArrays always have a LB of 0. Arrays passed from Excel always have a LB of 1. It's no big deal when iterating a single array - use For Each or LBound and UBound - but working with two arrays at once suddenly means you have to think about details like bounds and indices.
I really hate "Option Base". It's like a mysterious action at a distance, module-by-module, just to avoid typing in a lower bound. I know it pre-dates VB/VBA, though, and was relevant once upon a time.
I was wrong about the LB of an array created with Array() in my comment above. It is affected by the Option Base setting. ParamArrays are not, though.
This function will do as JohnFx suggested and allow for varied lengths on the arrays
Note to anyone finding this. it actually merges the arrays. doesn't keep the order of the elements if that's important to you.
I tried the code provided above, but it gave an error 9 for me. I made this code, and it worked fine for my purposes. I hope others find it useful as well.
It work if Lbound is different than 0 or 1. You Redim once at start
My preferred way is a bit long, but has some advantages over the other answers:
- It can combine an indefinite number of arrays at once
- It can combine arrays with non-arrays (objects, strings, integers, etc.)
- It accounts for the possibility that one or more of the arrays may contain objects
- It allows the user to choose the base of the new array (0, 1, etc.)
Unfortunately there is no way to append / merge / insert / delete elements in arrays using VBA without doing it element by element, different from many modern languages, like Java or Javascript .
It's possible using split and join to do it, like a previous answer has showed, but it is a slow method and it is not generic.
For my personal use, I've implemented a splice functions for 1D arrays, similar to Javascript or Java. splice get an array and optionally delete some elements from a given position and also optionally insert an array in that position
Following the @johannes solution, but merging without loosing data (it was missing first elements):
To join Array1 and Array2, create a new array say JointArray
Suitable for 2 one dimensional arrays of same or different sizes. Results could be checked with Debug.Print Join(Array1, ",") Debug.Print Join(Array2, ",") Debug.Print Join(JointArray, ",")
I would like to adapt the great idea from user3286479 to work with arrays that came from single column ranges:
Here's a version that uses a collection object to combine two 1-d arrays and pass them to a 3rd array. Doesn't work for multi-dimensional arrays.
Or even a way that either variable can be uninitialised or an empty array or an array of objects (eg Dictionary objects). Only handles one dimension at a time, though. Also, it APPENDS arr2 to arr1 rather than merges.
I really appreciated Buggabill's and Daniel McCracken's responses. I needed a function to combine multidimensional arrays, but I'm sure I'll use Daniel's in the future. I made a couple mods to Buggabill's to 1) accommodate multidimensional arrays with a mix of variables and objects, and 2) merge the two arrays sequentially rather than meshed together (since the two arrays are combined in each step of the For loop). See the Was/Now examples below for an illustration.
Hope this helps some of you.
Extension on Split approach using ArrayToText() function (MS365)
If you dispose of MS/Excel 365 you may simplify joins & splits ( see @user3286479 's most upvoted post ) by passing a so called jagged array (a.k.a. as array of arrays) as main argument. This jagged array may comprise two or even more arrays, not only arr1 and arr2 .
As a further benefit I included the option to decide whether the array returns the merged array elements consecutively (default value additive=True ) or not (i.e. intertwined with explicit argument additive=False ).
Example call
Results in VB Editor's immediate window
Caveat
A possible disadvantage of the approach above is that all elements would be returned as strings, thus including all numeric values as well. To avoid this situation, you might use the following function alternatively using FilterXML() (available btw since vers. 2013):
Классическая ситуация: у вас есть два списка, которые надо слить в один. Причем в исходных списках могут быть как уникальные элементы, так и совпадающие (и между списками и внутри), но на выходе нужно получить список без дубликатов (повторений):
Давайте традиционно рассмотрим несколько способов решения такой распространенной задачи - от примитивных "в лоб" до более сложных, но изящных.
Способ 1. Удаление дубликатов
Можно решить задачу самым простым путем - руками скопировать элементы обоих списков в один и применить потом к полученному набору инструмент Удалить дубликаты с вкладки Данные (Data - Remove Duplicates) :
Само-собой, такой способ не подойдет, если данные в исходных списках часто меняются - придется повторять всю процедуру после каждого изменения заново.
Способ 1а. Сводная таблица
Этот способ является, по сути, логическим продолжением предыдущего. Если списки не очень большого размера и заранее известно предельное количество элементов в них (например, не больше 10), то можно объединить две таблицы в одну прямыми ссылками, добавить справа столбец с единичками и построить по получившейся таблице сводную:
Как известно, сводная таблица игнорирует повторы, поэтому на выходе мы получим объединенный список без дубликатов. Вспомогательный столбец с 1 нужен только потому, что Excel умеет строить сводные по таблицам, содержащим, по крайней мере, два столбца.
При изменении исходных списков новые данные по прямым ссылкам попадут в объединенную таблицу, но сводную придется обновить уже вручную (правой кнопкой мыши - Обновить). Если не нужен пересчет "на лету", то лучше воспользоваться другими вариантами.
Способ 2. Формула массива
Можно решить проблему формулами. В этом случае пересчет и обновление результатов будет происходить автоматически и мгновенно, сразу после изменений в исходных списках. Для удобства и краткости давайте дадим нашим спискам имена Список1 и Список2, используя Диспетчер имен на вкладке Формулы (Formulas - Name Manager - Create) :
После именования, нужная нам формула будет выглядеть следующим образом:
На первый взгляд выглядит жутковато, но, на самом деле, все не так страшно. Давайте я разложу эту формулу на несколько строк, используя сочетание клавиш Alt+Enter и отступы пробелами, как мы делали, например тут:
Логика тут следующая:
Обратите внимание, что это формула массива, т.е. после набора ее нужно ввести в ячейку не обычным Enter , а сочетанием клавиш Ctrl + Shift + Enter и затем скопировать (протянуть) вниз на нижестоящие ячейки с запасом.
В английской версии Excel эта формула выглядит как:
=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1), 0)), INDEX(Список2, MATCH(0, COUNTIF($E$1:E1, Список2), 0))), "")
Минус у такого подхода в том, что формулы массива ощутимо замедляют работу с файлом, если в исходных таблицах большое (несколько сотен и более) количество элементов.
Способ 3. Power Query
Если в ваших исходных списках большое количество элементов, например, по несколько сотен или тысяч, то вместо медленной формулы массива лучше использовать принципиально другой подход, а именно - инструменты надстройки Power Query. Эта надстройка по умолчанию встроена в Excel 2016. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (бесплатно).
Алгоритм действий следующий:
- Открываем отдельную вкладку установленной надстройки Power Query (если у вас Excel 2010-2013) или просто идем на вкладку Данные (если у вас Excel 2016).
- Выделяем первый список и жмем кнопку Из таблицы/диапазона (From Range/Table) . На вопрос про создание из нашего списка "умной таблицы" - соглашаемся:
В будущем, при любых изменениях или дополнениях в исходных списках, достаточно будет лишь правой кнопкой мыши обновить таблицу результатов.
Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
Примеры этой главы предназначены для того, чтобы дать вам отправную точку. Но они, конечно же, не исчерпывают всех возможностей использования обычных функций Excel в сочетании с новыми свойствами динамических массивов. Всякий раз, когда функция ожидает скаляр, передача ей массива приведет к подъему. И в итоге функция вернет диапазон значений.
Использование СЕГОДНЯ() и ПОСЛЕД() для календаря
Допустим, вы используете СЕГОДНЯ() в качестве третьего аргумента функции ПОСЛЕД(). Тогда формула =ПОСЛЕД(6;7;СЕГОДНЯ()) в ячейке А4 вернет массив из 6 строк и 7 столбцов календаря, начинающегося сегодня:
Рис. 74. Использование СЕГОДНЯ() и ПОСЛЕД() для создания динамического календаря на ближайшие шесть недель
Добавьте формулу =ТЕКСТ(A4:G4; " ДДД " ) в ячейку А3 для заголовков дней недели. Обратите внимание, что эта формула разливается на 7 ячеек по горизонтали.
ТДАТА() и ПОСЛЕД() для вывода интервалов времени
Допустим вам нужно получить массив времени с интервалом 5 минут. Одна минута = 1/1440 суток. Поэтому, чтобы формула массива возвращала 5-минутные интервалы, используйте 1/288 в качестве аргумента шаг: =ПОСЛЕД(5;4;ТДАТА();1/288)
Рис. 75. 5-минутные интервалы, отсчитанные от текущего времени
Генерация последовательности букв алфавита
Функция СИМВОЛ() возвращает букву, основанную на коде ASCII. Например, СИМВОЛ(65) вернет A (английскую), а СИМВОЛ(90) – Z. Можно использовать ПОСЛЕД() внутри СИМВОЛ() для генерации последовательности букв, а затем передать этот массив в функцию ОБЪЕДИНИТЬ(), чтобы показать последовательность букв, разделенных дефисом (или любым другим символом). Формула в С3: =ОБЪЕДИНИТЬ( " — " ;ИСТИНА;СИМВОЛ(ПОСЛЕД(B6;1;КОДСИМВ(A6))))
Рис. 76. Генерация последовательных букв с разделителем
Заметим, что ASCII коды принимают значения от 0 до 255, поэтому если ввести строчную я, и указать число букв более 1, функция вернет ошибку, т.к., после я символов ASCII нет.
Несколько наибольших значений диапазона
Если вам нужно вернуть N наибольших значений диапазона, введите требуемое число в G1. Формула в G2 вернет N наибольших чисел, автоматически расширив область результата:
Рис. 77. Возвращает N наибольших значений диапазона
Несколько наименьших значений диапазона с изменением направления вывода
Усложним задачу. Нужно вывести N наименьших значений, но расположить их не по вертикали, а по горизонтали. Первое, что напрашивается, поместить функцию НАИМЕНЬШИЙ() внутрь функции ТРАНСП(): =ТРАНСП((НАИМЕНЬШИЙ(A1:E8;ПОСЛЕД(G1)))).
Рис. 78. Вернуть N наименьших значений в горизонтальном направлении
Посмотрите еще раз на рис. 78. Формула в G2 использует ПОСЛЕД(4) для возврата вертикального массива, который затем превращается в горизонтальный с помощью ТРАНСП(). Так почему же сразу не вернуть горизонтальный массив!? Для этого используйте внутри функцию ПОСЛЕД(1;G1). Эта формула используется в ячейке G3, и она намного короче формулы в G2.
Обратите также внимание, как в ячейках G6, G7 и G11 выводится текст формул. Это делается с помощью функции Ф.ТЕКСТ(). На рис. 78 формула в G6 сама является функцией динамического массива! Функция Ф.ТЕКСТ() ожидала на вход скаляр, а получила массив G2:G3, и разлилась на две ячейки G6 и G7.
Имитация сводной таблицы диапазоном с тремя формулами
Как автор книги Сводные таблицы в Microsoft Excel, я люблю хорошую сводную таблицу. Но менеджер Microsoft проекта динамических диапазонов Джо Макдэйд и MVP Excel Роджер Говьер указали, что три формулы, вполне способны заменить сводную таблицу. При этом не будет потребности в обновлении.
Чтобы построить отчет:
Рис. 79. Замена сводной таблицы тремя формулами; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Отображение чисел в двоичном, восьмеричном или шестнадцатеричном виде
Если нужно представить десятичные числа в двоичном, восьмеричном или шестнадцатеричном виде, используйте функции ОСНОВАНИЕ() и ПОСЛЕД(). Формула в А4: =ОСНОВАНИЕ(ПОСЛЕД(B1);;). У функции ОСНОВАНИЕ() три аргумента. Первый число здесь представлен функцией ПОСЛЕД(B1), которая задает массив целых чисел от 1 до 16. Второй – основание, здесь это массив констант . Третий – мин_длина, здесь также массив констант . Работа этой формулы дает пример попарного подъема.
Рис. 80. Генерация двоичных, восьмеричных и шестнадцатеричных чисел
Суммирование длин текстовых строк большого числа ячеек
Используйте формулу =СУММ(ДЛСТР(A2:A12)). Теперь для ее ввода не требуется Ctrl+Shift+Enter.
Рис. 81. Проверка длины ваших твитов
Текст по столбцам
Эта сложная формула разработана Риком Ротстайном. Благодаря использованию функции ПОСЛЕД(), вы можете разбить текстовую строку на слова с использованием лишь одной формулы в В1.
Рис. 82. Функция ПОСЛЕД() облегчает синтаксический анализ
Суммирование всех ВПР
В старом Excel это можно было сделать только с помощью старинной функции ПРОСМОТР(). На самом деле, это была одна из двух причин, по которой пользователи Excel добирались до функции ПРОСМОТР(). С динамическими массивами вы можете использовать ВПР. Вы всё еще будете использовать старую функцию ПРОСМОТР, когда у вас вектор поиска и вектор результатов ориентированы в противоположных направлениях. Единственная формула в Е9 выполняет сначала расчет массива ВПР, а потом суммирует отдельные значения: =СУММ(ВПР(C2:C26;E1:F6;2)). Обратите внимание, что поиск ВПР ведет приблизительно – четвертый аргумент опущен.
Рис. 83. Вычисление всех ВПР и последующее их суммирование
Объединение имени и фамилии
В этом примере используется попарный подъем, который описан в главе 9. Функция получает диапазон имен, один скаляр (пробел) и диапазон фамилий. Первый и последний вектор имеют одинаковый размер. Формула =ПРОПНАЧ(A2:A10& " " &B2:B10) возвращает массив 9×1.
Рис. 84. Объединение имени и фамилии и понижение регистра
Одна формула вместо таблицы данных «что если»
А в этом примере свойство трансляции (см. главу 9) используется для расчета ежемесячных платежей по автокредиту. Диапазон В4:F7 – динамический массив с единственной формулой в В4: =ПЛТ(C9/12;A4:A7;-B3:F3). Первый аргумент – процентная ставка за период. Обратите внимание, что используется простая относительная ссылка, так как не нужно протягивать формулу. Второй аргумент – общее число периодов выплаты по займу – вместо скаляра получает массив А4:А7. Третий аргумент – сумма кредита с обратным знаком – также вместо скаляра получает массив В3:F3. Благодаря трансляции Excel генерит массив из 4 строк и 5 столбцов и передает эти 20 значений в ПЛТ. Обратите внимание, условное форматирование отлично работает с динамическим массивом.
Рис. 85. Одна формула динамического массива в ячейке В4 заменяет таблицу данных
Условное форматирование на основе функции ЗНАК()
Вот еще один пример условного форматирования. Всякий раз, когда я добавляю набор значков «три треугольника» (который явно состоит из двух треугольников и прямоугольника… но я отвлекся), я использую функцию ЗНАК(), чтобы разделить все возможные значения на три группы: больше, равно и меньше. В примере ниже показаны цены закрытия акций компании DJI (ведущего китайского производителя дронов). Используется попарное поднятие для нахождения разницы двух массивов, смещенных один относительно другого на один день. Формула в C3: =ЗНАК(B3:B17-B4:B18) позволяет показать в какую сторону изменилась цена акций.
Рис. 86. Отражение динамики цен закрытия акций
Попарный подъем основан на двух массивах с одинаковым числом строк. В этом примере оба массива находятся в столбце B, но один начинается с B3 (для значения сегодняшнего дня), а другой – с B4 (для значения вчерашнего дня). На рисунке ниже показана формула в C3 в режиме редактирования, так что вы можете видеть два массива.
Рис. 87. Сравнение цен закрытия текущего и предыдущего дней
Это пример генератора анаграмм. Анаграмма – литературный прием, состоящий в перестановке букв слова или фразы, которая в результате дает другое слово или словосочетание. В оригинале автор использует английскую фразу, буквы которой переставляет случайным образом. Случай редко приводит к осмысленному результату, но позволяет получить что-то любопытное. При переводе я использовал слово из статьи Анаграммы.
Рис. 88. Генератор анаграмм с использованием динамических массивов
Использование ссылки на массив как части ссылки
Создание элементов календаря
Рис. 90. Генерация элементов календаря
Прогнозирование
Рис. 91. Прогноз на следующие 12 месяцев
Более сложное прогнозирование
В предыдущем примере мы вычислили массив из 12 ячеек, каждая из которых содержала месячный прогноз. В примере ниже этот массив помещается в функцию СУММ, чтобы вернуть прогноз на год. Формула усложняется, и для ее понимания, аргументы в нотации разнесены на отдельные строки:
Рис. 92. Годовой прогноз продаж
Функция ПРЕДСКАЗ.ETS ожидает скаляр, но вместо этого вы передаете ей массив из 12 значений, используя ПОСЛЕД() внутри КОНМЕСЯЦА(). Прогноз основан на фактических данных за 2007–2018 гг., расположенных в ячейках В2:B145. Формулу из E2 можно скопировать в E3:E6.
Возможно, вы спросите: почему нужно пять отдельных формул? Нельзя ли заменить ссылку на год – D2 ссылкой на массив ПОСЛЕД(5;1;2019)? Ответ – нельзя. С двумя вертикальными массивами в одной функции Excel попытается сделать попарный подъем. Но массивы имеют разный размер, поэтому вернется ошибка.
Транспонирование одного массива для предотвращения попарного подъема
Одна из стратегий предотвращения попарного подъема состоит в том, чтобы расположить годы по горизонтали. Одна формула в E3 возвращает массив 12*5:
Рис. 93. Если разместить годы по горизонтали, формула работает
Прогнозирование всех пяти лет в одной формуле
Если вы передадите ПРЕДСКАЗ.ETS в функцию СУММ, то получите суммарный прогноз продаж на 60 месяцев:
Рис. 94. Одна формула возвращает 5-летний прогноз
Данные в строке (столбце) разместить в диапазоне
Предположим, что у вас есть вектор чисел (D1:O1), и вы хотите разместить его в диапазон шириной N столбцов. Введите количество столбцов в D3. Тогда формула =ЕСЛИОШИБКА(ИНДЕКС(E1:AH1;ПОСЛЕД(15;D3));»») вернет диапазон шириной три столбца. Поскольку вы не знаете сколько понадобится строк, и чтобы избежать появления ошибок в нижней части диапазона, вы поместили вашу формулу внутрь функции ЕСЛИОШИБКА().
Рис. 95. Данные из строки в массив из трех столбцов
Использование динамических массивов для зависимой проверки
Функция проверки данных позволяет выбирать из выпадающего списка. Это прекрасно работает, пока кто-то не захочет иметь два списка. Причем так, чтобы элементы во втором списке зависели от того, что выбрано в первом. Это называется зависимой проверкой.
Рис. 96. Зависимая проверка
Как VBA работает с динамическими массивами?
Существуют миллионы строк кода VBA, которые используют синтаксис Range.Formula. Если и в вашем коде есть такие конструкции, Excel применит неявное пересечение, и вы не получите динамический массив, который мог бы разлиться. Код VBA будет работать также, как и до введения динамических массивов, и поэтому код останется рабочим.
Если вы пишете код для версии Excel, поддерживающей динамические массивы, используйте новый синтаксис Range.Formula2. Такой код создаст формулу, которая потенциально может разлиться. На рисунке ниже первая строка кода пыталась сгенерировать =ПОСЛЕД(10), но Excel автоматически добавил оператор @ неявного пересечения, так что код вернет в ячейку А1 только одно значение – единицу. Напротив, формула в L1 разольется и вернет динамический массив:
Рис. 97. Для вывода динамического диапазона используется новый синтаксис Range.Formula2
Аналогично код Range.FormulaR1C1 вернет одно значение, а Range.Formula2R1C1 – динамический диапазон.
Незаметно для меня Microsoft совершил прорыв, представив в сентябре 2018 новые возможности – динамические массивы. Ниже – перевод книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. Книги серии Straight to the Point предназначены для глубокого освещения одного аспекта Excel.
Содержание
Введение
Ранее многие люди пытались изучать формулы массива и потерпели неудачу. Майк Гирвин написал отличную книгу, чтобы объяснить, как работают формулы массива. Целые главы той книги здесь будут сведены к коротким предложениям, благодаря новым функциям.
О динамических массивах было объявлено 24 сентября 2018 года, но даже в MS Excel 2019 они пока не представлены. Динамические массивы доступны только в Office 365. Я думаю, что парадигма покупки бессрочной лицензии на Office каждые три или шесть лет устарела, и рекомендую переходить на подписку.
Оригинальные файлы с примерами можно загрузить с сайта автора. К каждой главе я приложу файл, адаптированный к настоящему переводу.
Глава 1. Начало работы
Формулы теперь могу разливаться
Начнем с базовой формулы массива. Перейдите в ячейку Е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 в ответ на мои первые видео с динамическими массивами был: можно ли вы копировать и вставлять значения? Да! Выберите диапазон E3:G11, нажмите Ctrl+C, кликните правой кнопкой мыши на выбранную новую ячейку и выберите Специальная вставка –> Значения.
Что происходит, если формула не может пролиться?
Что произойдет, если ячейка, куда должен разлиться диапазон, будет занята?
Рис. 4. Как Excel справится с занятой ячейкой?
Как только вы очистите ячейки, мешающие размещению массива, он автоматически разольется.
- Неопределенный размер. Вы не можете использовать волатильные функции, например, СЛУЧМЕЖДУ(), в качестве аргумента функции ПОСЛЕДОВ().
- Выходит за пределы листа. Вы не можете ввести функцию =СОРТ(C:C) в ячейке E2.
- Табличная формула. Вы не можете использовать функции динамического массива внутри Таблицы.
- Не хватает памяти. Вам следует ссылаться на диапазон меньшего размера.
- Разлив в объединенные ячейки. Динамический массив не может разливаться в объединенную ячейку.
- Неопознанная ошибка. Excel не может распознать ошибку.
Если ваша формула указывает на Таблицу, динамический массив будет расширяться при добавлении новых строк в Таблицу
Рис. 6. Динамический массив «отслеживает» Таблицу
Сравните с рис. 2. Вы преобразовали диапазон А1:С19 в Таблицу (Ctrl+T). Формула в ячейке Е3 изменилась на =Таблица1. Теперь, если вы добавите еще одну строку в Таблицу (А11:С11), формула в ячейке Е3 не изменится, а динамический массив автоматически расширится еще одной строкой (Е12:G12).
Хотя формулы динамического массива могут указывать на Таблицу, в самой Таблице использовать формулы динамического массива нельзя.
Что такое неявное пересечение
Если вы введете =@C2:C11 в любой ячейке в строках со 2 по 10, формула вернет значение из столбца С той строки, в которой вы ввели формулу. Знак @ известен как неявный оператор пересечения.
Пусть однотипные значения хранятся в нескольких списках, расположенных в разных столбцах (см. файл примера ).
Как видно из рисунка выше, названия городов, относящиеся к разным странам, размещены в соответствующих столбцах.
Вообще, хранить однотипные данные в разных столбцах в MS EXCEL не правильно: возникают сложности с использованием стандартных инструментов ( Сводные таблицы , Автофильтр и др.) и созданием таблиц (например, чтобы каждому городу соотнести его численность придется создавать новый лист или новый список городов).
Более правильно хранить перечень городов вот в такой таблице.
Платой за такое "правильное" размещение является необходимость ввода повторных значений стран.
СОВЕТ: О советах по созданию таблиц с правильной структурой читайте в статье Советы по построению таблиц в MS EXCEL.
Тем не менее, иногда встречаются задачи, в которых такое размещение данных оправдано (см. Многоуровневый связанный список в MS EXCEL ).
Как было сказано выше, с такой таблицей не удобно работать, поэтому объеденим значения, расположенные в нескольких столбцах, в один столбец с помощью обычных формул.
Аналогичная задача была решена в статье Объединение 3-х и более списков в MS EXCEL . Здесь мы решим ее без формул массива , но нам понадобится создать дополнительный лист с формулами.
Решение
На листе Связи выведем перечень всех стран с листа Города и подсчитаем количество городов у каждой страны = СЧЁТЗ(СМЕЩ(города!$B$2;;ПОИСКПОЗ(A2;города!$B$1:$V$1;0)-1;21)) .
Количество городов позволит нам определелить не только количество раз, которое нужно повторить название страны в итоговой таблице, но и смещение для каждой страны. Т.е. страна США будет иметь смещение 1 (т.е. в таблице идет первой), затем через 12 строк (12 - число городов в США из таблицы на листе Города) идет страна Мексика (ее смещение =13, т.е. начинается с 13-й строки таблицы) и т.д.
Смещение рассчитаем с помощью формулы = ЕСЛИ(B2;СУММ($B$1:B1)+1;-1)
Примечание : в функции СУММ() использована относительная и абсолютная ссылка на В1 .
Теперь создадим таблицу, в которой все города будут выведены в один столбец (см. лист таблица в файле примера ).
Сначала сформируем сквозную нумерацию городов в столбце А (см. статью Числовые последовательности в MS EXCEL (порядковые номера 1,2,3. и др.)
Затем, используя связку функций ИНДЕКС() и ПОИСКПОЗ() , выведем названия стран нужное количество раз (количество соответствует количеству городов) с помощью формулы = ИНДЕКС(Связи!$A$2:$A$22;ПОИСКПОЗ(A8;Связи!$C$2:$C$22;1)) .
Примечание: функции ИНДЕКС() и ПОИСКПОЗ() позволяют сформировать аналог " левого ВПР() ". Подробнее - здесь .
Нам также понадобится счетчик городов для каждой страны =СЧЁТЕСЛИ($B$7:B8;B8)
Читайте также: