Как в Excel Продлить Строку в Таблице • Способ 1 формулы
Трансформация столбца в таблицу
Представьте, что после выгрузки из какой-либо корпоративной ERP-системы, базы данных или программы вы получили данные в виде длинного столбца, а для работы вам из них нужно скомпоновать нормальную двумерную таблицу:
Очевидно, что вручную этим заниматься очень долго и муторно, поэтому давайте разберем несколько способов сделать это красиво.
Способ 1. Формулы
Числа слева (1,8,15. ) — это номера строк, начинающих каждый блок. Числа сверху (0,1,2,3. ) — это сдвиг внутри блока. Теперь, чтобы получить адреса нужных нам ячеек можно ввести простую формулу, склеивающую букву столбца с номером строки:
Обратите внимание на корректное закрепление знаками доллара строк и столбцов — это важно при копировании ссылки из первой ячейки на весь зеленый диапазон.
И осталось превратить нашу текстовую ссылку в полноценную. В этом нам поможет функция ДВССЫЛ (INDIRECT) , о которой я подробно уже писал:
Способ 2. Power Query
Для начала, превратим наш диапазон в «умную таблицу» — для этого данные нужно выделить и нажать сочетание клавиш Ctrl + T или выбрать на вкладке Главная команду Форматировать как таблицу (Home — Format as Table) . В появившемся затем окне важно снять галочку Таблица с заголовками (My table has headers) , т.к. «шапки» у нас нет:
После загрузки наши данные появятся в новом окне Power Query:
Теперь добавляем к данным столбец индекса (т.е., фактически, имитируем нумерацию строк) через вкладку Добавление столбца — Столбец индекса (Add Column — Index Column) :
Теперь свернем нашу таблицу, конвертируя числа в получившемся столбце в заголовки новых колонок. Для этого используем команду Столбец сведения на вкладке Преобразовать (Transform — Pivot Column) :
Осталось заполнить получившиеся пустоты (null) во всех столбцах, кроме двух последних командой Заполнить вниз на вкладке Преобразовать (Transform — Fill Down) :
Удалить ненужные столбцы (первый и последний) и отфильтровать лишние строки (убрать галочку Null в фильтре по столбцу 5). Заголовки столбцов можно переименовать двойным щелчком для пущей приятности, а также настроить подходящие форматы данных с помощью иконок в шапке:
В будущем, если исходная таблица с данными поменяется в размерах или в содержимом, достаточно будет просто обновить наш запрос, щелкнув по финальной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) .
Способ 3. Макрос из надстройки PLEX
Если у вас установлена моя надстройка PLEX для Excel 2007-2016, то можно все сделать еще быстрее и проще. Выделяем исходные данные и жмем на вкладке PLEX — Трансформация — Изменить размеры (Transform — Resize) :
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
10 способов сэкономить время при помощи горящих клавиш в Excel — ExcelGuide: Про Excel и не только
- Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
- Первая строка таблицы должна содержать короткие, уникальные заголовки;
- Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
- Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
- В таблице не должно быть пустых строк и абсолютно пустых столбцов.
Аналогично следует поступить и в случае присутствия одинаковых строчек. Команда «удалить дубликаты» выполнит поиск абсолютно идентичных значений строк и удалит все, оставив только одну. Выделять в этом случае следует весь массив значений.
Как добавить строку в таблице Excel: все способы
- Выберите линию или группу линий, которые вы хотите развернуть, на панели координат вертикального сектора. Щелкните выделение правой кнопкой мыши. Контекстное меню запускается. Выбираем внутри него пункт «Высота строки…».
Для перемещения таблицы в новое место на том же листе переместите указатель к любой из ее границ. Когда указатель мыши превратится в крестик с четырьмя стрелками, щелкните и перетащите таблицу на новое место. Для перемещения таблицы на другой лист (в той же книге или в другой книге) выполните следующие действия.
Увеличение таблиц
Если же вам нужно очистить лист от данных, независимо от того, были ли они оформлены как диапазон или как таблица, то выделите все ячейки с данными и воспользуйтесь клавишей DELETE или удалите соответствующие столбцы.
Excel Совет №10. Формат по образцу несвязанных ячеек
Этим же маркером можно добавлять в таблицу новые строки, смещая его вниз. Управляя маркером в любом направлении, мы управляем количеством строк и столбцов, которое должна содержать таблица. Нельзя только смещать маркер по диагонали, чтобы одновременно добавлять / удалять строки и столбцы.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.