Как в 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:

Окно Power Query с данными

Теперь добавляем к данным столбец индекса (т.е., фактически, имитируем нумерацию строк) через вкладку Добавление столбца — Столбец индекса (Add Column — Index Column) :

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

Остаток от деления на 7

Теперь свернем нашу таблицу, конвертируя числа в получившемся столбце в заголовки новых колонок. Для этого используем команду Столбец сведения на вкладке Преобразовать (Transform — Pivot Column) :

Сворачиваем в столбцы

Осталось заполнить получившиеся пустоты (null) во всех столбцах, кроме двух последних командой Заполнить вниз на вкладке Преобразовать (Transform — Fill Down) :

Заполнение вниз

Удалить ненужные столбцы (первый и последний) и отфильтровать лишние строки (убрать галочку Null в фильтре по столбцу 5). Заголовки столбцов можно переименовать двойным щелчком для пущей приятности, а также настроить подходящие форматы данных с помощью иконок в шапке:

Убираем лишнее и переименовываем столбцы

Выгружаем результаты на лист

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

Способ 3. Макрос из надстройки PLEX

Если у вас установлена моя надстройка PLEX для Excel 2007-2016, то можно все сделать еще быстрее и проще. Выделяем исходные данные и жмем на вкладке PLEX — Трансформация — Изменить размеры (Transform — Resize) :

Как сделать таблицу в Excel. Пошаговая инструкция - ЭКСЕЛЬ ХАК

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность. Если же вы хотите что-то уточнить, я с радостью помогу!
Можно легко и быстро настроить ширину столбца в соответствии с ее содержанием и избавиться от этих бесполезных ##### записей. Нажмите на заголовок столбца, переместите курсор в правый край заголовка и кликните мышкой два раза.

10 способов сэкономить время при помощи горящих клавиш в Excel — ExcelGuide: Про Excel и не только

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

Аналогично следует поступить и в случае присутствия одинаковых строчек. Команда «удалить дубликаты» выполнит поиск абсолютно идентичных значений строк и удалит все, оставив только одну. Выделять в этом случае следует весь массив значений.

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Выбираем нужную ячейку или массив кликом левой кнопки мышки, правой вызываем плавающее меню, в котором выбираем команду удалить. Если же вы хотите что-то уточнить, я с радостью помогу!
Открытую запароленную книгу (файл) надо переименовать. Для этого выбираем «сохранить как» и в диалоговом окне сохранения файла нажимаем меню «сервис» (левый нижний угол), потом – «общие параметры». В полях «Пароль для открытия» и Пароль для изменения» удаляются звездочки. Далее «Ок» и «Сохранить». В новом окне нажмите кнопку «Да», чтобы заменить запароленный файл новым.

Как добавить строку в таблице Excel: все способы

  1. Выберите линию или группу линий, которые вы хотите развернуть, на панели координат вертикального сектора. Щелкните выделение правой кнопкой мыши. Контекстное меню запускается. Выбираем внутри него пункт «Высота строки…».

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

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Технические характеристики и ограничения листа и книги Параметр Максимальное значение Количество открытых книг Ограничено объемом доступной оперативной памяти и ресурсами системы Общее количество строк и столбцов на листе 1 048 576 строк и 16 384 столбца Ширина столбца 255 знаков Высота строки 409 пунктов. Если же вы хотите что-то уточнить, я с радостью помогу!
Сразу скажу, что есть два основных способа увеличения таблицы: увеличивая размер отдельных ее элементов (строк, столбцов) и применяя масштабирование. В последнем случае диапазон таблиц будет пропорционально увеличен. Этот параметр разделен на два отдельных метода: масштабирование до экрана и печать. Теперь давайте рассмотрим каждый из этих методов более подробно.
Как в Excel Продлить Строку в Таблице • Способ 1 формулы

Увеличение таблиц

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

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

Excel Совет №10. Формат по образцу несвязанных ячеек

Этим же маркером можно добавлять в таблицу новые строки, смещая его вниз. Управляя маркером в любом направлении, мы управляем количеством строк и столбцов, которое должна содержать таблица. Нельзя только смещать маркер по диагонали, чтобы одновременно добавлять / удалять строки и столбцы.

Оставить отзыв

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