Развернись, массив! Запевай, душа… аналитика

Редактор таблиц
Дополняем диапазон столбцами и строками

Функции Expand для редакторов электронных таблиц появилась буквально год назад или чуть больше. Конечно же мы создали свой аналог «Развернуть» в «Р7», который и представили в начале осени. На первый взгляд, у функции пока не так много примеров использования, но один из них – заполнение тестовых датасетов, и он точно будет востребован. Хотя есть и более приземленные варианты.

Функция для разворачивания массивов

Если вы помните недавний пост про плагин для импорта html-таблиц, то там был тестовый файл, заполненный традиционным «lorem ipsum». Он был создан предельно примитивно: выделили нужное число с трок и столбцов, скопипастили текст, получили диапазон нужного размера. Но если бы на нам был нужен массив, тут как раз пригодилась бы функция РАЗВЕРНУТЬ.

Посмотрите на пример ниже. Изначально текст «lorem ipsum» был набран только в ячейке В1, но мы развернули ее до массива в 30 строк и 19 столбцов, заполненных тестовым текстом. Если не видно на иллюстрации, то в строке формул введено =РАЗВЕРНУТЬ(B1;30;19;”lorem ipsum”) в фигурных скобках, то есть для ввода использовалась комбинация клавиш Shift+Ctrl+Enter.

Развернись, массив! Запевай, душа… аналитика
Пример заполнения массива с помощью функции РАЗВЕРНУТЬ

Функцию можно ввести вручную или найти через диалоговое окно «Вставить функцию» в разделе «Поиск и ссылки» (сразу после нашей любимой функции ПРОСМОТРХ). 

Развернись, массив! Запевай, душа… аналитика
Диалоговое окно «Вставить функцию» в редакторе «Р7»

Согласно описанию функция «Развернуть» возвращает массив, расширенный путем добавления строк и столбцов. Синтаксис выглядит так:

=РАЗВЕРНУТЬ(массив, строки, [столбцы], [заполняющее_значение])

где

  • массив — это массив, который нужно расширить, на примере выше это вообще одна ячейка, но ниже мы покажем другие варианты,
  • строки – это количество строк в возвращаемом массиве. Необязательный аргумент, если указано количество столбцов. Если значение не указано, строки просто не будут добавлены,
  • [столбцы] — это количество столбцов в возвращаемом массиве. Необязательный аргумент, если указано количество строк. Если значение не указано, столбцы не будут добавлены,
  • [заполняющее_значение] — это значение для заполнения добавленных ячеек (цифры, текст, по умолчанию выдает ошибку #Н/Д).

Сочетание функций

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

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

Для этого пригодится простая функция ВСТОЛБИК, которая появилась в редакторе электронных таблиц «Р7» прошлым летом с дополнением 7.3. Мы не делали о ней отдельного поста, потому что там все проще простого по сути и по синтаксису: перечислите в скобках через точку с запятой все диапазоны, которые вы хотите собрать в массив, например =ВСТОЛБИК(B51:D53;B56:D58).

Развернись, массив! Запевай, душа… аналитика
Пример использования функции ВСТОЛБИК

Примечание. Чтобы договорить эту тему, напомним, что у поисковой функции ВСТОЛБИК есть близнец ГСТОЛБИК, который позволяет присоединять ячейки уже не вертикально, а горизонтально. Синтаксис тот же самый – перечисление диапазонов через точку с запятой (это можно не запоминать, редактор всё подскажет). И конечно не забудьте про ввод как для формул массива с помощью комбинации клавиш Shift+Ctrl+Enter.

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

=ВСТОЛБИК(B51:D53;B56:D58;РАЗВЕРНУТЬ(B61:B63;;3;”?”))

Развернись, массив! Запевай, душа… аналитика
Пример использования функции РАЗВЕРНУТЬ внутри функции ВСТОЛБИК

Но мы же не просто так собрали данные, мы хотели с ними что-то сделать. Давайте вспомним про функцию СОРТ, про которую мы писали в декабре, и напишем такую формулу

=СОРТ(ВСТОЛБИК(B51:D53;B56:D58;РАЗВЕРНУТЬ(B61:B63;;3;”?”));3;-1)

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

Развернись, массив! Запевай, душа… аналитика
Пример совместного использования функций СОРТ, ВСТОЛБИК и РАЗВЕРНУТЬ

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


В финале такого поста будет логичным напомнить о других недавних постах, посвященных функциях редактора электронных таблиц «Р7»:

Если вы немного устали от таблиц и функций, можете воспользоваться рубрикатором и поиском, которые расположены справа от этого текста, посмотреть популярные и похожие статьи. Помимо этого у нас на сайте есть база знаний в карточках, а еще много увлекательного контента в VK и в Telegram. Там же есть возможность написать нам, поделиться своим опытом и задать вопросы. До связи!

Оцените статью
Блог Р7-Офис
Добавить комментарий