Как преобразовать таблицу в список в excel
Демонстрация Transpose Table Dimension
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Нажмите Кутулс >> Диапазон >> Перенести размеры таблицы. Смотрите скриншоты:
Преобразование перекрестной (двумерной) таблицы в список
1. Выберите кросс-таблицу, которую вы хотите преобразовать в список, как показано ниже, а затем примените утилиту.
2. Выбрать Перекрестная таблица в список вариант и укажите одну ячейку, чтобы развернуть результат в Диапазон результатов. Внимание: Вы можете указать диапазон результатов для разных листов и книг.
3. Нажмите OK. Двумерная таблица была преобразована в таблицу списка. Смотрите скриншоты:
Перекрестная таблица (с заголовками строк и столбцов)
Таблица списка (одномерная таблица)
Преобразование списка в перекрестную (двумерную) таблицу
1. Выберите список, который вы хотите преобразовать в кросс-таблицу, а затем примените утилиту. Внимание: вы должны выбрать список, который содержит 3 столбца.
2. Выбрать Список для перекрестной таблицы вариант и укажите одну ячейку, чтобы развернуть результат в Диапазон результатов. Внимание: Вы можете указать диапазон результатов для разных листов и книг.
3. Нажмите OK. Список преобразован в кросс-таблицу. Смотрите скриншоты:
Таблица списка (одномерная таблица)
Перекрестная таблица (с заголовками строк и столбцов)
1. Перекрестная (двухмерная) таблица будет преобразована в плоский список с тремя столбцами.
2. При преобразовании списка в кросс-таблицу список должен содержать три столбца.
3. Этот инструмент поддерживает расстегивать (Ctrl + Z).
4. Если вы выбрали список, который содержит заголовки, как показано ниже (см. Снимок экрана), вы должны проверить У моих данных есть заголовки вариант, иначе операция преобразования не будет работать должным образом.
Демонстрация Transpose Table Dimension
Kutools for Excel: с более чем 300 удобными функциями, вы можете попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.
Kutools for Excel
Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.
Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 60 дней.
Рис. 167.1. Преобразование сводной таблицы в список
Хитрость создания такого списка заключается в использовании сводной таблицы. Но прежде чем вы сможете применить этот метод, вы должны добавить команду Мастер сводных таблиц на панель быстрого доступа. Excel 2007, Excel 2010 и Excel 2013 все еще поддерживают Мастера сводной таблицы, но он недоступен на ленте. Чтобы получить доступ к мастеру, выполните следующие действия.
После выполнения этих шагов ваша панель быстрого доступа будет содержать новый значок.
Теперь пришло время преобразовать сводную таблицу в список. Имейте в виду, что, хотя следующие шаги специфичны для образцов данных, приведенных здесь, вы можете легко изменять шаги для работы с вашими данными.
- Активизируйте любую ячейку в сводной таблице.
- Щелкните на значке Мастер сводных таблиц и диаграмм, который вы добавили на панель быстрого доступа.
- В диалоговом окне Мастер сводных таблиц и диаграмм установите первый переключатель в положение в нескольких диапазонах консолидации и нажмите кнопку Далее.
- В шаге 2а установите переключатель в положите Создать поля страницы и нажмите кнопку Далее.
- В шаге 2b в поле Диапазон укажите диапазон сводной таблицы ( А1:Е4 для выборки из примера) и нажмите кнопку Добавить; затем нажмите кнопку Далее, чтобы перейти к шагу 3.
- В шаге 3 выберите место для сводной таблицы и нажмите кнопку Готово. Excel создаст сводную таблицу с данными и покажет область Список полей сводной таблицы.
- В области Список полей сводной таблицы снимите флажки Строка и Столбец.
Так, сводная таблица остается только с полем данных Сумма по полю Значение. На данный момент небольшая сводная таблица показывает только сумму всех значений (рис. 167.2).
Рис. 167.2. Эта небольшая сводная таблица может быть расширена
Чтобы завершить, щелкните два раза на ячейке, содержащей итоговое значение (2851 в этом примере). Excel создаст новый лист, который будет содержать исходные данные в виде таблицы (рис. 167.3). Заголовки столбцов представляют общее описание (Строка, Столбец и Значение), так что вы, вероятно, захотите изменить их и сделать более информативными.
Как преобразовать кросс-таблицу (двумерную таблицу) в список в Excel?
Вы знаете, как преобразовать кросс-таблицу (двумерную таблицу) в таблицу-список (одномерную таблицу) в Microsoft Excel? По некоторым причинам вам может потребоваться преобразовать таблицу (с заголовками строк и заголовков столбцов) в плоский список в Excel. Следующие приемы помогут легко и быстро преобразовать кросс-таблицу в плоский список:
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Предположим, у вас есть кросс-таблица, и вам нужно преобразовать ее в список следующим образом.
Преобразование кросс-таблицы в список в Excel с помощью VBA
Следующий макрос VBA может помочь вам быстро преобразовать все перекрестные таблицы на активном листе в плоские списки.
Шаг 1: Удерживайте другой + F11 ключей в Excel, и откроется окно Microsoft Visual Basic для приложений.
Шаг 2: нажмите Вставить > Модулии вставьте следующий макрос в окно модуля.
VBA при транспонировании кросс-таблицы в список:
Подложка ConvertTableToList ()
Константа TEST_COLUMN как String = "A"
Dim i до тех пор, j до тех пор
Dim iLastRow As Long
Dim iLastCol до тех пор
Приложение.ScreenUpdating = False
С ActiveSheet
iLastRow = .Cells (.Rows.Count, TEST_COLUMN) .End (xlUp) .Row
Для i = iLastRow To 2 Step -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
Затем я
.Rows (1) .Удалить
Конец с
Application.ScreenUpdating = True
End Sub
Шаг 3: нажмите F5 ключ для запуска этого макроса.
Затем вы увидите преобразованный список, показанный на следующем снимке экрана:
Примечание:
1. Этот макрос VBA теряет некоторые данные исходной таблицы, такие как заголовки или другие. В этом случае заголовки (имя, оценка и класс) теряются.
2. Вы можете изменить As String = "A", чтобы удовлетворить ваши потребности. Например, если кросс-таблица указана в столбце E, вы должны вместо «E» вместо «A».
3. И если на листе есть другие данные, иногда данные в списке будут неправильными после преобразования кросс-таблицы.
Преобразование перекрестных таблиц в списки в Excel с помощью Kutools for Excel
Если у вас есть Kutools for Excel установлен, вы можете использовать его Перенести размеры таблицы инструмент для быстрого преобразования кросс-таблиц в плоский список.
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.
Шаг 1: Выберите таблицу, которую вы преобразуете в список.
Шаг 2: нажмите Кутулс >Изменить > Перенести размер таблицы. См. Следующий снимок экрана:
Шаг 3. В диалоговом окне «Транспонировать размеры таблицы» установите флажок Перекрестная таблица в список вариант и выберите Диапазон результатов нажав на кнопку.
Шаг 4: нажмите OK кнопку.
Затем вы увидите, что исходная таблица преобразована в плоский список, как на следующем снимке экрана:
Перенести размеры таблицы также можно преобразовать список в таблицу кросс-таблицы. Для получения дополнительной информации перейдите на Перенести размеры таблицы.
Как быстро преобразовать таблицу массивов в таблицу списка в Excel?
Предположим, у вас есть таблица массивов / кросс-таблица, и преобразование этой таблицы массивов в таблицу-список может быть для вас несложной задачей, и вы можете преобразовать ее, вводя данные один за другим. Однако, если для преобразования в таблицы списков необходимо несколько таблиц массивов, ручной метод не может быть хорошим решением. Теперь в этом руководстве представлены приемы, с помощью которых можно быстро преобразовать таблицу массивов в таблицу списка в Excel.
Преобразование таблицы массивов в таблицу списка с помощью Kutools for Excel
Преобразование таблицы массивов в таблицу списка с помощью сводной таблицы
Вы можете применить сводную таблицу для преобразования таблицы массивов в список, а затем скопировать ее как диапазон.
1. Нажмите Alt + D одновременно, а затем нажмите P ключ для включения Мастер сводных таблиц и диаграмм диалог и проверьте Несколько диапазонов консолидации и PivotTable параметры. Смотрите скриншот:
2. Нажмите «Далее», чтобы перейти к шагу 2а мастера, и установите флажок «Я создам поля страницы». Смотрите скриншот:
3. Нажмите Далее идти на Шаг 2b мастера, и выберите диапазон массива и добавьте его в Все диапазоны список. Смотрите скриншот:
4. Нажмите Далее чтобы перейти к последнему шагу, отметьте нужный вариант. Смотрите скриншот:
5. Нажмите Завершить а затем была создана сводная таблица, затем перейдите к ячейке пересечения Общий итог, дважды щелкните по нему, и вы увидите, что сводная таблица была создана на новом листе. Смотрите скриншоты:
6. Затем выберите список PivotTable и щелкните правой кнопкой мыши, чтобы выбрать Настольные > Преобразовать в диапазон из контекстного меню. Смотрите скриншот:
Теперь таблица массивов преобразована в таблицу списка.
Преобразование таблицы массивов в таблицу списка с помощью VBA
Вот VBA, который может помочь вам преобразовать таблицу массивов в таблицу списка, выполните следующие действия:
1. Нажмите Alt + F11 для Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте ниже код VBA в новый Модули окно.
VBA: преобразовать таблицу массивов в список
3. И нажмите F5 нажмите клавишу для запуска VBA, и появится диалоговое окно, в котором вы можете выбрать таблицу массива, исключая заголовки. Смотрите скриншот:
4. Нажмите OK для перехода к следующему диалоговому окну, чтобы выбрать ячейку для вывода таблицы списка. Смотрите скриншот:
5. Нажмите OK, и таблица массивов была преобразована в список.
Наконечник:
Если вы хотите заполнить пустые ячейки на основе указанной выше ячейки, вы можете сделать следующее:
1. Установить Kutools for Excel - удобный инструмент, а затем нажмите Кутулс > Вставить > Заполнить пустые ячейки. Смотрите скриншот:
2. в Заполнить пустые ячейки диалог, проверьте На основе ценностей и вниз параметры. Смотрите скриншот:
3. Нажмите Ok or Применить, и теперь пустые ячейки заполняются на основе приведенных выше значений.
Преобразование таблицы массивов в таблицу списка с помощью Kutools for Excel
Если вышеперечисленные методы для вас недостаточно просты, здесь я могу представить удобный инструмент - Kutools for Excel, Его Перенести размеры таблицы Утилита может быстро и легко конвертировать между таблицей массивов и таблицей списков.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите таблицу массивов, которую вы хотите преобразовать в список, и нажмите Кутулс > Диапазон > Перенести размеры таблицы. Смотрите скриншот:
2. Затем в Перенести размеры таблицы выберите вариант Перекрестная таблица в список, а затем выберите диапазон, который выводит таблицу списка. Смотрите скриншот:
3. Нажмите Ok, теперь таблица массивов преобразована в таблицу-список.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Тип данных выберите пункт Список.
Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Не знаете, какой параметр выбрать в поле Вид?
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.
Щелкните поле Источник и выделите на листе список допустимых элементов.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.
Значения также можно ввести непосредственно в поле Источник через запятую.
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите пункт Список.
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: