Как превратить таблицу в список excel

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

Возможно, вы уже знакомы с таким понятием Excel, как сводные таблицы, которые создаются из списка. Но что если вы хотите выполнить обратную операцию? В этом приеме вы узнаете, как создать список из простой сводной таблицы с двумя переменными.

Лист, показанный на рис. 167.1, отображает тот тип преобразования, о котором я говорю. Диапазон А1:Е4 содержит исходную сводную таблицу: 48 точек данных. Столбцы G:I показывают часть 48-строковой таблицы, полученную из сводной таблицы. Другими словами, каждое значение в исходной сводной таблице преобразуется в строку, которая также содержит соответствующие значению название продукта и месяц. Этот тип списка полезен, поскольку его можно отсортировать и манипулировать им другими способами.

Рис. 167.1. Преобразование сводной таблицы в список

Хитрость создания такого списка заключается в использовании сводной таблицы. Но прежде чем вы сможете применить этот метод, вы должны добавить команду Мастер сводных таблиц на панель быстрого доступа. Excel 2007, Excel 2010 и Excel 2013 все еще поддерживают Мастера сводной таблицы, но он недоступен на ленте. Чтобы получить доступ к мастеру, выполните следующие действия.

1. Щелкните правой кнопкой мыши на панели быстрого доступа и выберите в контекстном меню пункт Настройка панели быстрого доступа.
2. В разделе Панель быстрого доступа диалогового окна Параметры Excel выберите Команды на ленте из раскрывающегося списка слева.
3. Прокрутите список и выберите пункт Мастер сводных таблиц и диаграмм.
4. Нажмите кнопку Добавить.
5. Нажмите 0К, чтобы закрыть диалоговое окно Параметры Excel.

После выполнения этих шагов ваша панель быстрого доступа будет содержать новый значок.

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

1. Активизируйте любую ячейку в сводной таблице.
2. Щелкните на значке Мастер сводных таблиц и диаграмм, который вы добавили на панель быстрого доступа.
3. В диалоговом окне Мастер сводных таблиц и диаграмм установите первый переключатель в положение в нескольких диапазонах консолидации и нажмите кнопку Далее.
4. В шаге 2а установите переключатель в положите Создать поля страницы и нажмите кнопку Далее.
5. В шаге 2b в поле Диапазон укажите диапазон сводной таблицы ( А1:Е4 для выборки из примера) и нажмите кнопку Добавить; затем нажмите кнопку Далее, чтобы перейти к шагу 3.
6. В шаге 3 выберите место для сводной таблицы и нажмите кнопку Готово. Excel создаст сводную таблицу с данными и покажет область Список полей сводной таблицы.
7. В области Список полей сводной таблицы снимите флажки Строка и Столбец.

Так, сводная таблица остается только с полем данных Сумма по полю Значение. На данный момент небольшая сводная таблица показывает только сумму всех значений (рис. 167.2).

Рис. 167.2. Эта небольшая сводная таблица может быть расширена

Чтобы завершить, щелкните два раза на ячейке, содержащей итоговое значение (2851 в этом примере). Excel создаст новый лист, который будет содержать исходные данные в виде таблицы (рис. 167.3). Заголовки столбцов представляют общее описание (Строка, Столбец и Значение), так что вы, вероятно, захотите изменить их и сделать более информативными.

Как превратить таблицу в список excel

Как преобразовать кросс-таблицу (двумерную таблицу) в список в Excel?

Вы знаете, как преобразовать кросс-таблицу (двумерную таблицу) в таблицу-список (одномерную таблицу) в Microsoft Excel? По некоторым причинам вам может потребоваться преобразовать таблицу (с заголовками строк и заголовков столбцов) в плоский список в Excel. Следующие приемы помогут легко и быстро преобразовать кросс-таблицу в плоский список:

• Преобразование кросс-таблицы в список в Excel с помощью VBA
• Преобразование кросс-таблицы в список в Excel с помощью Kutools for 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 кнопку.

Затем вы увидите, что исходная таблица преобразована в плоский список, как на следующем снимке экрана:

Перенести размеры таблицы также можно преобразовать список в таблицу кросс-таблицы. Для получения дополнительной информации перейдите на Перенести размеры таблицы.

Лаб-2 Работа со списками

Цель работы: Освоить технологию обработки списков, как основного элемента базы данных.

Содержание работы:

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

Представление электронной таблицы в виде списка

Список — это один из способов организации данных на рабочем листе. Список создается как помеченный ряд, состоящий из строк с однотипными данными. Например, перечень студентов группы, в котором столбцы имеют соответственно следующие имена: Ф.И.О., Отдел, Всего начислено, Всего удержано, Сумма к выдаче, представляет собой список данных. Данные, организованные в список, в терминологии Excel называются базой данных (БД). При этом строки таблицы — это записи БД, а столбцы — это поля записей БД.

Чтобы превратить таблицу Excel в список, необходимо присвоить столбцам имена, которые будут использоваться как имена полей записей. Имена столбцов могут состоять из нескольких строк, как это показано на рис. 1.

Рис. 1. Пример списка

При создании списка на рабочем листе Excel необходимо выполнить следующие правила:

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

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

Список может занимать весь рабочий лист: 16384 строки и 256 столбцов.

Имена столбцов должны располагаться в первой строке списка. Excel использует эти имена при создании отчетов, в поиске и сортировке данных.

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

Чтобы отделить имена столбцов от данных, следует разместить рамку по нижнему краю яччек строки с именами столбцов. Не рекомендуется использовать пустые строки или пунктирные линии.

Сортировка данных

Список можно отсортировать по алфавиту, по значению или в хронологическом порядке в соответствии с содержанием определенного поля. Чтобы отсортировать весь список, достаточно выделить одну ячейку или выбрать в меню данные команду Сортировка, Excel автоматически выделит весь список. Если в первой строке списка находятся имена полей, то они не будут включены в сортировку. Необходимо иметь в виду, что в этом случае итоговая строка исходного списка также будет включена в сортировку, поэтому целесообразнее самостоятельно выделять область исходного списка для сортировки.

Команда СОРТИРОВКА осуществляется через диалоговое окно. В трех полях входа в окно СОРТИРОВКА можно задать ключи, по которым будет выполнена сортировка. В первом поле (в списке) необходимо выбрать столбец, по которому Excel должен отсортировать данные. Например, данные сводной ведомости можно отсортировать по столбцу ОТДЕЛ. Во втором поле диалогового окна СОРТИРОВКА можно задать следующий ключ сортировки, например Ф.И.О. Действие третьего ключа сортировки аналогично (рис. 2).

В диалоговом окне СОРТИРОВКА имеется режим ПАРАМЕТРЫ. Он позволяет устанавливать порядок сортировки по первому ключу — обычный или определяемый пользователем, задать учет строчных и прописных букв (учет регистра символов), а также направление сортировки — по возрастанию или убыванию.

Для быстрой сортировки по панели инструментов СТАНДАРТНАЯ находятся две кнопки:

сортировать по возрастанию;

сортировать по убыванию.

Ключом сортировки в этом случае является столбец с текущей ячейкой.

Рис.2. Результат сортировки списка по полю ФИО

Как видно из рисунка, теперь данные в таблице отсортированы по фамилиям, в то время как в списке рис.1 – сортировка произведена по отделам.

Фильтрация данных в списке

С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью простого средства — автофильтра. Более сложные запросы к базе данных можно реализовать с помощью команды РАСШИРЕННЫЙ ФИЛЬТР. Использование этой функции дает возможность реализовать операцию, обычно имеющуюся в СУБД для реализации поиска информации в базе данных в интерактивном режиме.

Чтобы использовать автофильтр, надо сначала выделить область списка для поиска с заголовками полей. Затем выполнить команду АВТОФИЛЬТР в меню ДАННЫЕ.

По команде АВТОФИЛЬТР Excel помещает раскрывающиеся списки непосредственно в имена столбцов списка (рис. 3).

Рис.3. Вид экрана после применения команды АВТОФИЛЬТР

Щелкнув по стрелке, можно вывести на экран список всех уникальных элементов соответствующего столбца. Если выделить соответствующий элемент столбца, то будут скрыты все строки, кроме тех, которые содержат выделенное значение. Например, если выбрать значение ОТДЕЛ равное 1, то будут выбраны только те сотрудники, которые работают в первом отделе (рис. 4).

Рис.4. Результат фильтрации по полю «Отдел»

Элемент столбца, который выделен в раскрывающемся списке, называется критерием фильтра. Можно продолжить фильтрацию списка с помощью критерия из другого столбца. Например, если после выделения элемента “1” в раскрывающемся списке ОТДЕЛ в списке ВСЕГО НАЧИСЛЕНО выбрать значение “500”, то на экран будет выведена только одна строка для сотрудника Сидорова.

Чтобы удалить критерии фильтра для отдельного столбца, надо выбрать параметр ВСЕ в раскрывающемся списке. Чтобы показать все скрытые в списке строки, надо выбрать в меню ДАННЫЕ команду ФИЛЬТР, а затем — команду ПОКАЗАТЬ ВСЕ.

С помощью автофильтра можно для каждого столбца задать пользовательский критерий, надо в раскрывающемся списке выбрать параметр НАСТРОЙКА. а затем в диалоговом окне ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР ввести нужные критерии.

Сложная фильтрация

Для фильтрации списка или базы данных по сложному критерию, который будет определен ниже, а также для получения части исходного списка по нескольким заданным столбцам в Excel используется команда РАСШИРЕННЫЙ ФИЛЬТР меню ДАННЫЕ (рис. 5). Отличие этой команды от команды АВТОФИЛЬТР состоит в том, что, кроме перечисленных выше возможностей, отфильтрованные записи можно вынести в другое место рабочего листа Excel, не испортив исходный список.

Чтобы использовать команду РАСШИРЕННЫЙ ФИЛЬТР, надо сначала создать таблицу критериев, которую следует разместить на том же рабочем листе, что и исходный список, но так, чтобы не скрывать лист во время фильтрации. Для формирования таблицы критериев необходимо скопировать имена полей списка в ту часть рабочего листа, где будет располагаться таблица критериев. При этом число строк в этой таблице определяется только количеством критериев поиска. Однако включение пустых строк в таблицу критериев недопустимо, поскольку в этом случае будут найдены все записи списка. На рис. 6 показано, что рядом с исходной таблицей, в которой необходимо вести поиск, создана таблица критериев в ячейках H5-H7. В ячейках А19-С19 создана таблица для отображения результатов поиска.

Задание критерие списка в виде констант требует точной копии имен тех столбцов исходного списка, которые задают условия фильтрации. Например, для фильтрации списка Ведомость начислений с целью отбора записей сотрудников, у которых сумма к выдаче больше 900 и меньше 1150, надо создать таблицу критериев, показанную в ячейках Н5-Н7 на рис.6.

Рис. 6. Формирование таблицы критериев и таблицы результатов поиска для применения операции РАСШИРЕННЫЙ ФИЛЬТР

На рис.7 показано, как после использования операции РАСШИРЕННЫЙ ФИЛЬТР Excel формирует адреса яччек всех трех таблиц для осуществления поиска и вывода результатов в указанное место.

Рис. 7. Вид диалогового окна РАСШИРЕННЫЙ ФИЛЬТР

На рис.7 показано, что заданы все необходимые параметры для осуществления операции поиска и записи результатов в заданное место. Осталось только щелкнуть мышью на кнопке ОК.

В связи с тем, что Excel в автоматическом режиме осуществляет поиск по заданным критериям , необходимо точное задание адресов исходной таблицы, адресов таблицы, где заданы критерии для поиска и удреса таблицы конечных результатов. Точного задания требуют не только задание адресов, но и названий полей таблиц, так как в режиме поиска происходит сравнение и названий. Тогда экран примет вид, показанный на рис. 8.

Рисунок8. Результаты выполнения поиска в режиме РАСШИРЕННЫЙ ФИЛЬТР

Как видно из таблицы, Excel точно выполнил условия задания.

Построить таблицу увеличив базовые цифры примера в графе «Всего начислено» на величину – 500 умноженное на порядковый номер студента в списке группы.

Выполнить сортировку списка сначала по фамилиям, а затем по полю «Сумма к выдаче».

Осуществить поиск минимального, а затем максимального значения суммы к выдаче.

Выполнить поиск по полю суммы к выдаче в указанном преподавателем диапазоне.

На рисунке 9 приведены исходные данные для выполнения лабораторной работы.

Выпадающий список в Excel с помощью инструментов или макросов

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

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

Создание раскрывающегося списка

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

Ввести значения, из которых будет складываться выпадающий список, можно разными способами:

1. Вручную через «точку-с-запятой» в поле «Источник».
2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
5. Сохраняем, установив тип файла «с поддержкой макросов».
6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

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

1. Создадим первый выпадающий список, куда войдут названия диапазонов.
2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
3. Жмем «Свойства» – открывается перечень настроек.
4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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