Как преобразовать таблицу в список в Excel
Обновлено: 21.11.2024
Как преобразовать кросс-таблицу (двухмерную таблицу) в список в Excel?
Знаете ли вы, как преобразовать перекрестную таблицу (двумерную таблицу) в таблицу списка (одномерную таблицу) в Microsoft Excel? По некоторым причинам вам может потребоваться преобразовать таблицу (с заголовками строк и столбцов) в простой список в Excel. Следующие приемы помогут вам легко и быстро преобразовать кросс-таблицу в плоский список:
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!
Предположим, у вас есть кросс-таблица и вам нужно преобразовать ее в список следующим образом.
Следующий макрос VBA поможет вам быстро преобразовать все кросс-таблицы на активном листе в плоские списки.
Шаг 1. Удерживая клавиши Alt + F11 в Excel, откроется окно Microsoft Visual Basic для приложений.
Шаг 2. Нажмите «Вставить» > «Модуль» и вставьте следующий макрос в окно модуля.
VBA для транспонирования кросс-таблицы в список:
Sub ConvertTableToList()
Const TEST_COLUMN As String = "A"
Размер i As Long, j As Long
Затемнение iLastRow As Long
Затемнение iLastCol As Long
Application.ScreenUpdating = False
С ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
Для i = iLastRow To 2 Шаг -1
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
Для j = iLastCol To 3 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 2).Value = .Cells(i, j).Value
.Cells(i, j).Value = ""
Следующее j < br />Далее
.Rows(1).Удалить
Конец с
Application.ScreenUpdating = True
Конец подписки
Шаг 3. Нажмите клавишу F5, чтобы запустить этот макрос.
Затем вы увидите преобразованный список, показанный на следующем снимке экрана:
Примечание:
<р>1. Этот макрос VBA теряет некоторые данные исходной таблицы, такие как заголовки или другие. В этом случае заголовки (Имя, Оценка и Класс) теряются. <р>2. Вы можете изменить As String = "A", чтобы удовлетворить ваши потребности. Например, если кросс-таблица указана в столбце E, вы должны вместо "E" вместо "A". <р>3. И если на листе есть другие данные, иногда данные в списке будут неправильными после преобразования кросс-таблицы.Если у вас установлен Kutools for Excel, вы можете использовать его инструмент Transpose Table Dimensions для быстрого преобразования кросс-таблиц в плоский список.
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.
Шаг 1. Выберите таблицу, которую вы хотите преобразовать в список.
Шаг 2: Нажмите Kutools > Изменить > Транспонировать размер таблицы. См. следующий снимок экрана:
Шаг 3. В диалоговом окне "Транспонировать размеры таблицы" установите флажок "Перекрестить таблицу в список" и выберите диапазон "Результаты", нажав кнопку.
Шаг 4. Нажмите кнопку "ОК".
Затем вы увидите, что исходная таблица преобразуется в плоский список, как показано на следующем снимке экрана:
Транспонировать размеры таблицы также можно преобразовать список в кросс-таблицу. Дополнительную информацию см. в разделе Транспонирование размеров таблицы.
Пока мы работаем в Excel с таблицей данных, по какой-то причине нам может понадобиться преобразовать таблицу Excel (с заголовками строк и столбцов) в список. Следующие методы помогут вам легко и быстро преобразовать кросс-таблицу в список.
Загрузить книгу с практиками
Вы можете скачать бесплатный шаблон Excel отсюда и попрактиковаться самостоятельно.
3 быстрых способа преобразовать таблицу в список в Excel
Способ 1. Мастер создания сводных таблиц и диаграмм для преобразования таблицы в список в Excel
Давайте сначала познакомимся с нашим набором данных. Я расставил продажи некоторых продавцов по трем неделям. Вы заметите, что есть разные заголовки для строки 2 и столбца B. Мы хотим составить список, в котором будут все заголовки недель в одном столбце и суммы продаж в другом столбце. Итак, теперь мы преобразуем эту таблицу данных в список с помощью мастера сводных таблиц и сводных диаграмм. Это довольно длительная процедура, но простая.
Сначала мы добавим мастер сводных таблиц и диаграмм в верхнюю строку меню Excel.
Шаг 1:
➥ Нажмите «Файл», и вы увидите различные варианты.
Шаг 2:
➥ Затем нажмите меню «Параметры».
Откроется окно параметров Excel.
Шаг 3:
➥ Затем щелкните следующим образом: «Настроить ленту» > «Команды не на ленте» > «Мастер сводных таблиц и диаграмм».
Шаг 4:
➥ После этого нажмите последовательно: Данные > Новая группа > Новая группа (Пользовательская) > Добавить
Вскоре вы увидите опцию «Мастер сводных таблиц и диаграмм», как показано на рисунке ниже.
Шаг 5:
➥ Теперь просто нажмите OK, и вы получите параметр Мастер сводных таблиц и диаграмм в верхней строке меню.
Шаг 6:
➥ Нажмите последовательно: Данные > Мастер сводных таблиц и диаграмм
Появится диалоговое окно.
Шаг 7:
➥ Отметьте несколько диапазонов консолидации и сводную таблицу
➥ Затем нажмите Далее
Шаг 8:
➥ Позже нажмите переключатель «Создать для меня одно поле страницы» и нажмите «Далее».
Шаг 9:
➥ Теперь выберите диапазон данных и снова нажмите "Далее".
Шаг 10:
➥ Выберите нужный рабочий лист. Я выбрал Новый рабочий лист.
➥ Нажмите "Готово".
Поля сводной таблицы появятся в правой части окна Excel.
Шаг 11:
➥ Отмените выбор строки и столбца
➥ Наконец, просто дважды щелкните по сумме значений.
Теперь вы заметили, что мы нашли нужный список на новом листе.
Похожие чтения:
- Как создать таблицу в Excel (с настройкой)
- Использование срезов для фильтрации таблицы в Excel 2013
- Существует ли функция ТАБЛИЦА в Excel?
Способ 2. Откройте Power Query, чтобы переключить таблицу на список в Excel
Power Query — это такой инструмент, который используется для упрощения процесса сбора данных из разных источников и может быть отсортирован в таблицу Excel в удобном и удобном для использования формате. Здесь мы будем использовать его для преобразования таблицы Excel в список.
Шаг 1:
➥ Выберите диапазон данных
➥ Затем щелкните следующим образом: Данные > Из таблицы/диапазона
Откроется диалоговое окно «Создать таблицу».
Шаг 2:
➥ Просто нажмите OK сейчас.
Появится новое окно с названием «Редактор Power Query».
Шаг 3:
➥ Нажмите и удерживайте клавишу Ctrl, затем, щелкнув левой кнопкой мыши, выберите три столбца недели в этом новом окне.
➥ Затем нажмите: Преобразовать > Отменить свод столбцов
Вскоре после этого вы найдете список, подобный изображенному ниже.
Шаг 4:
➥ Теперь просто нажмите "Закрыть и загрузить".
Тогда вы увидите, что мы получили ожидаемый список на новом листе.
Способ 3. Внедрение Excel VBA для преобразования таблицы в список в Excel
Если вам нравится программировать, то эту задачу можно выполнить с помощью Excel VBA. Давайте посмотрим, как это сделать.
Шаг 1:
➥ Щелкните правой кнопкой мыши заголовок листа.
➥ Выберите «Просмотреть код» в контекстном меню.
Откроется окно VBA.
Шаг 2:
➥ Напишите приведенные ниже коды-
➥ Затем нажмите значок воспроизведения, чтобы запустить коды.
Вскоре после этого вы получите нужный список на новом листе.
Заключение
Я надеюсь, что всех описанных выше методов будет достаточно для преобразования таблицы в список. Не стесняйтесь задавать любые вопросы в разделе комментариев и, пожалуйста, дайте мне обратную связь.
Как быстро преобразовать таблицу массива в таблицу списка в Excel?
Предположим, что у вас есть таблица-массив/кросс-таблица, и преобразование этой таблицы-массива в таблицу-список может оказаться для вас несложной задачей, и вы можете преобразовать ее, введя данные один за другим. Однако, если для преобразования в таблицы списка необходимо несколько таблиц массивов, ручной метод не должен быть хорошим решением. Теперь в этом руководстве представлены приемы, которые могут быстро преобразовать таблицу массива в таблицу списка в Excel.
Преобразование таблицы массива в таблицу списка с помощью Kutools for Excel
Вы можете применить сводную таблицу для преобразования таблицы массива в список, а затем скопировать ее как диапазон.
<р>1. Одновременно нажмите клавиши Alt + D, а затем нажмите клавишу P, чтобы открыть диалоговое окно мастера сводных таблиц и диаграмм, и установите флажок Несколько диапазонов консолидации и параметры сводной таблицы. Смотрите скриншот:
<р>2. Нажмите «Далее», чтобы перейти к шагу 2а мастера, и установите флажок «Я создам параметр полей страницы». Смотрите скриншот:
<р>3. Нажмите «Далее», чтобы перейти к шагу 2b мастера, выберите диапазон массивов и добавьте его в список «Все диапазоны». Смотрите скриншот:
<р>4. Нажмите «Далее», чтобы перейти к последнему шагу, отметьте нужный вариант. Смотрите скриншот:
<р>5. Нажмите «Готово», после чего будет создана сводная таблица, затем перейдите к пересекающейся ячейке общего итога, дважды щелкните ее, и вы увидите, что сводная таблица списка была создана на новом листе. Смотрите скриншоты:
<р>6. Затем выберите список сводных таблиц и щелкните правой кнопкой мыши, чтобы выбрать «Таблица»> «Преобразовать в диапазон» в контекстном меню. Смотрите скриншот:
Теперь таблица массива преобразована в таблицу списка.
Вот VBA может помочь вам преобразовать таблицу массива в таблицу списка, выполните следующие шаги:
<р>1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений. <р>2. Нажмите «Вставить» > «Модуль» и вставьте приведенный ниже код VBA в новое окно модуля.VBA: преобразование таблицы массива в список
<р>3. И нажмите клавишу F5, чтобы запустить VBA, и появится диалоговое окно, в котором вы можете выбрать таблицу массива, исключая заголовки. Смотрите скриншот:
<р>4. Нажмите OK, чтобы перейти к следующему диалоговому окну, чтобы выбрать ячейку для вывода таблицы списка. Смотрите скриншот:
<р>5. Нажмите OK, и таблица массива будет преобразована в список.
Совет:
Если вы хотите заполнить пустые ячейки на основе указанной выше ячейки, вы можете сделать следующее:
<р>1. Установите Kutools for Excel – удобный инструмент, а затем нажмите Kutools > Вставить > Заполнить пустые ячейки. Смотрите скриншот:
<р>2. В диалоговом окне «Заполнить пустые ячейки» установите флажок «На основе значений» и «Вниз». Смотрите скриншот:
<р>3. Нажмите "ОК" или "Применить", и теперь пустые ячейки будут заполнены в соответствии с указанными выше значениями.
Если описанные выше методы не достаточно просты для вас, здесь я могу представить удобный инструмент - Kutools for Excel, его утилита Transpose Table Dimensions может быстро и легко конвертировать таблицу массива и таблицу списка.
После бесплатной установки Kutools for Excel сделайте следующее:
<р>1. Выберите таблицу массивов, которую вы хотите преобразовать в список, и нажмите Kutools > Диапазон > Транспонировать размеры таблицы. Смотрите скриншот:
<р>2. Затем в диалоговом окне «Транспонировать размеры таблицы» установите флажок «Перекрестить таблицу в список», а затем выберите диапазон, который выводит таблицу списка. Смотрите скриншот:
<р>3. Нажмите "ОК", теперь таблица массива преобразована в таблицу списка.
Excel — очень полезный инструмент для анализа данных, и всегда полезно освоить как можно больше навыков, чтобы сэкономить время и силы на работе. Один общий вопрос: как я могу преобразовать кросс-таблицу (также известную как двумерная таблица) в плоский список (также известный как одномерный список) в Excel? Хотя плоский список – идеальная форма для анализа, данные, которые мы получаем в повседневной жизни, обычно представлены в виде кросс-таблиц, поэтому необходимо знать, как выполнять преобразование.
Кросс-таблица и плоский список
Если вы не знакомы с перекрестными таблицами и плоскими списками, вот как они выглядят:
Кросс-таблица/двумерная таблица: общая форма для записи данных
Плоский список/одномерный список: идеальная форма для анализа данных
Можете ли вы найти разницу между этими двумя формами? В нижнем списке вы можете видеть, что метрика за год (показана первой строкой в верхней кросс-таблице) была преобразована в один столбец, что означает, что это уже не метрика, а список данных.
Как преобразовать кросс-таблицу в простой список
Если у вас уже есть функция мастера сводных таблиц и диаграмм в верхнем меню, вы можете пропустить шаги 1–3 и сразу перейти к шагу 4.
1) В верхнем меню выберите "Файл".
Затем нажмите «Параметры» на левой панели.
2) На левой панели выберите «Настроить ленту»; на правой панели в разделе «Выбрать команды из» выберите «Команды не на ленте» и найдите мастер сводных таблиц и диаграмм в списке ниже.
3) Разверните категорию «Данные» и нажмите «Новая группа». Когда элемент «Новая группа (настраиваемый)» создан, щелкните, чтобы выделить мастер сводных таблиц и сводных диаграмм в левом списке, и нажмите «Добавить» >>. Теперь функция мастера сводных таблиц и диаграмм должна быть добавлена в Данные > Новая группа (Пользовательская).
По завершении не забудьте нажать кнопку "ОК", чтобы сохранить изменения.
4) Вернитесь в окно Excel и нажмите Мастер сводных таблиц и диаграмм на вкладке Данные.
5) Во всплывающем окне выберите Несколько диапазонов консолидации и Сводная таблица. Затем нажмите Далее >.
6) Нажмите "Далее" >.
7) В разделе «Диапазон:» выберите все необходимые данные и нажмите «Добавить». В случае успеха выбранный диапазон должен появиться в разделе «Все диапазоны». По завершении нажмите Далее >.
8) Выберите "Новый рабочий лист" и нажмите "Готово".
9) В новой сводной таблице отмените выбор параметров «Строка» и «Столбец».
10) Дважды щелкните число рядом с общей суммой.
11) Ну вот! Одномерный список должен быть создан на новом листе.
На этом короткое руководство подошло к концу. Надеюсь, это помогло вам решить вашу проблему! Если у вас есть дополнительные вопросы или идеи, не стесняйтесь оставлять комментарии ниже.
Дополнительная информация:
Хотите продвинуться по карьерной лестнице?
Попробуйте Udacity – платформу электронного обучения для учащихся, ориентированных на технологии!
Никогда не поздно освоить новые технические навыки. Неважно, идет ли речь о программировании, анализе данных или других областях. Все, что вам нужно, это немного страсти, немного настойчивости и немного профессионального руководства. Udacity сделает за вас последнюю часть.
Для любого экономного ученика Driver Easy Coupons предлагает актуальные предложения, купоны и промокоды, которые действительно работают. Прежде чем отправиться к кассе, почему бы не заглянуть на страницу купонов Udacity и не сэкономить несколько долларов?
Что делать, если промокод не работает?
Убедитесь, что вы ввели промокод и ваша покупка соответствует всем ограничениям, таким как минимальная сумма расходов, региональное использование, только для новых клиентов и т. д. Если код по-прежнему не работает, это может быть просроченный или неточный купон. . Хотя наша цель — предоставлять только действительные купоны, к сожалению, мы не можем гарантировать, что время от времени на нашем веб-сайте будет появляться недействующий купон или купон с истекшим сроком действия.
Перед завершением покупки обязательно подтвердите, что все купоны были применены.
Читайте также: