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

Рубрика: 7. Полезняшки Excel

Часто табличные данные импортируются в Excel как один столбец (рис. 1). В столбце А содержится информация о сотрудниках, и каждая запись состоит из трех последовательных ячеек в одном столбце — указываются имя, отдел, местоположение. Наша цель — преобразовать эти данные, чтобы каждая запись занимала одну строку и была распределена по трем столбцам. [1]

%d1%80%d0%b8%d1%81-1-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d0%b5-%d1%80%d0%b0%d1%81%d0%bf%d0%be%d0%bb%d0%be%d0%b6%d0%b5%d0%bd%d1%8b-%d0%bf%d0%be-%d0%b2%d0%b5%d1%80%d1%82%d0%b8%d0%ba%d0%b0%d0%bb%d0%b8

Рис. 1. Данные расположены по вертикали; их нужно правильно распределить по трем столбцам

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

Преобразовать данные такого типа можно несколькими способами. Здесь будет предложен метод, основанный на функции ДВССЫЛ (подробнее см. Примеры использования функции ДВССЫЛ). Введите следующую формулу в ячейку С1, а потом скопируйте ее вниз и по строкам: =ДВССЫЛ("A"&СТОЛБЕЦ()-2+(СТРОКА()-1)*3)

Преобразованные данные занимают диапазон С1:Е4 (рис. 2). Формула работает с данными, расположенными по вертикали. Формула предназначена для ситуации, когда каждая запись занимает три идущие подряд строки в столбце, но формулу можно изменить так, чтобы она охватывала любое количество последовательных ячеек в столбце. Для этого нужно заменить число 3 в формуле на другое число. Например, если одна запись занимает пять строк в столбце, пользуйтесь следующей формулой: =ДВССЫЛ("A"&СТОЛБЕЦ()-2+(СТРОКА()-1)*5).

%d1%80%d0%b8%d1%81-2-%d0%b2%d0%b5%d1%80%d1%82%d0%b8%d0%ba%d0%b0%d0%bb%d1%8c%d0%bd%d1%8b%d0%b5-%d0%b4%d0%b0%d0%bd%d0%bd%d1%8b%d0%b5-%d0%bf%d1%80%d0%b5%d0%be%d0%b1%d1%80%d0%b0%d0%b7%d0%be%d0%b2%d0%b0

Рис. 2. Вертикальные данные, преобразованные в таблицу

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 157, 158.

Комментарии: (1)

Спасибо. Этапять.


Прокомментировать