Как быстро преобразовать таблицу массивов в таблицу списка в Excel?

Второй проблемой, возникающей при использовании перекрестных таблиц, являются данные в нарисованных от руки «мега-таблицах», структура которых не поддается анализу. Моя цель в этой статье — описать, как быстро преобразовать это «горе-творение» в массив для построения перекрестной таблицы.

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример	69 Кб	2402

Как видите, у нас есть эта таблица, хотя я бы не назвал это таблицей:

Для преобразования этой таблицы нам понадобится надстройка YoXCEL.

Преобразуйте нашу таблицу в ее первоначальный вид. Удалите из таблицы итоговые столбцы, перейдите в правый конец и удалите столбцы:

Далее нам нужно заполнить пустые строки в столбцах «Месяц» и «Менеджер». Для этого вернитесь в начало таблицы и выделите первые ячейки, содержащие данные в этих столбцах, в нашем случае это ячейки «A3:B3». В главном меню перейдите на вкладку YOXCEL и нажмите на кнопку «Таблицы», затем выберите из выпадающего списка «Заполнить пустым»:

В появившемся диалоговом окне нажмите кнопку «OK» и нажмите кнопку «OK»:

Получаем следующий результат…

Поместите курсор в ячейку «A2» и включите фильтр. Отфильтруйте все строки в столбце «А», содержащие слово «Итого», и удалите:

Сбросьте фильтр для столбца «A». Для столбца «B» повторите эти шаги:

Сбросьте фильтр в столбце «B» и выберите всю таблицу. Нажмите кнопку «Таблицы» на вкладке YOXCEL главного меню, затем в выпадающем меню выберите «Преобразовать таблицу в таблицу»:

Чтобы открыть мастер, нажмите кнопку «Далее»:

Нажмите кнопку «Далее» на следующей вкладке мастера:

На следующей вкладке мастера ответьте на вопрос ‘Transpose row data?’ В нашем случае ответьте ‘No’ и нажмите кнопку ‘Next’:

На следующей вкладке мастера ответьте на вопрос «Исключить пустые строки? » В нашем случае ответьте «Да» и нажмите кнопку «Конвертировать»:

Мы ждем. Получаем такой массив:

Перейдите к ячейке «F1» и введите имя столбца «Имя»:

Перейдите к ячейке «A1» и создайте разворотную таблицу (Как создать разворотную таблицу?):

Автоматический перенос данных из одной таблицы в другую в программе Excel.

Вот три способа автоматического перемещения данных с одного листа Excel на другой.

Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа -вставка данных при помощи опции специальной вставки.

Давайте шаг за шагом рассмотрим связь двух таблиц.

Первый шаг.

Таблица, из которой данные будут переведены во вторую таблицу, должна быть выделена цветом.

Второй шаг.

Скопируйте информацию, нажав ctrl+C или щелкнув правой кнопкой мыши по контекстному меню и выбрав пункт меню «Копировать».

Третий шаг.

Перейдите на лист в документе «Excel», на который вы намерены перевести информацию в первой таблице.

Четвертый шаг.

Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»

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

Результат добавления ссылок

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При таком методе сводная таблица становится второй таблицей («приемником»).

Чтобы обновить сводную таблицу

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

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

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Однако этот метод подходит только для пользователей Excel 2016 и Excel 2013 и выше с установленным дополнением «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

Из таблицы -Power Query

Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».

Источник данных в Power Query

Окно конфигурации нового вида таблицы открывается после выбора области данных. С помощью этого окна можно изменить последовательность столбцов и удалить ненужные столбцы.

Выберите вид таблицы, затем нажмите кнопку «Закрыть и загрузить».

Если вы хотите обновить таблицу, просто щелкните правой кнопкой мыши на имени нужного запроса в правой части страницы (список «Book Queries»). Используя выпадающее контекстное меню, щелкните правой кнопкой мыши и выберите «Обновить».