Отменить свертывание столбцов в excel
Проблема не в том, что у нас нехватка данных, проблема в том, что у нас их слишком много; что просто уже сложно найти суть в этих данных. Вам нужен способ переорганизации и очистки ваших данных, чтобы сделать их полезными. Microsoft Excel это очень удобный инструмент, но вам всё равно нужно почистить данные, прежде чем их можно будет анализировать и просматривать.
Вот где Power Query вступает в игру. Power Query, также называемый «Подключение и преобразование данных», находится в Excel и автоматизирует процесс очистки данных.
Одним из самых популярных шагов очистки данных является получение данных из столбцов и преобразование их в строки. Поднимите ваши знания Excel на следующий уровень, научившись «отменять свёртывание» данные с помощью Microsoft Power Query. В этом уроке вы узнаете, как использовать Power Query для преобразования столбцов в строки в Excel.
Зачем преобразовывать столбцы в строки?
Прежде чем мы начнём, вам может быть интересно, почему полезно использовать данные, которые расположены в столбцах и преобразовать их в строки.
Ответ немного технический. В принципе, предпочтительнее, если каждая строка данных подобна записи, единственному указателю данных. Данные в столбцах смешиваются по несколькими атрибутами, распределенным в столбцах.
В приведенном ниже примере вы можете увидеть, как выглядят данные в Excel, если преобразовать их из столбцов в строки. С левой стороны, находится набор записей о клиентах и разными типами проектов в столбцах. С правой стороны я отменил свёртывание данных и конвертировал их в формат строки.
Преобразование столбцов в строки в Microsoft Excel.
Кроме того, если вы пользуетесь сводными таблицами, очень важно, чтобы ваши данные были в строках, а не столбцах, при создании сводной таблицы. Просто данные в столбцах загружаются в сводные таблицы не правильно.
Вкратце: преобразование данных из столбцов в строки облегчает работу. Power Query делает это запросто, в пару кликов. Давайте узнаем, как:
Как быстро преобразовать столбцы в строки в Excel (смотри и учись)
Если вы никогда раньше не использовали Power Query, я настоятельно рекомендую посмотреть этот быстрый двухминутный видеоурок ниже, чтобы посмотреть, как я работаю с ним. Вы узнаете, как преобразовать ваши данные в столбцах в строки (записи) в Excel, чтобы упростить работу с ними.
Давайте также рассмотрим написанную версию этих инструкций, чтобы узнать больше о Power Query и «отмене свёртывания» данных в Excel.
Знакомство с Microsoft Power Query для Excel
Power Query, формально называемый «Подключение и преобразование данных» в Excel 2016, это инструмент с двумя основными функциями:
Power Query — это мощный инструмент, который располагается в Excel. Вместо того, чтобы вручную переупорядочивать данные и повторять этот процесс, Power Query позволяет вам создать набор шагов, которые вы можете применить к данным повторно.
Установка Power Query
Power Query встроен в Excel 2016, но вы можете установить его для более ранних версий Excel в качестве надстройки. К сожалению, Power Query доступен только для Excel в Windows.
Чтобы установить Power Query для Excel в Windows (требуется только для Excel 2013 и Excel 2010, поскольку эта функция встроена в Excel 2016) перейдите на веб-сайт Microsoft, чтобы загрузить и установить надстройку Power Query.
Отправка данных в Power Query
Power Query используется в Excel, но он открывается в новом окне, которое находится поверх Excel.
Чтобы отменить свёртывание данные в Excel, вам сначала необходимо конвертировать данные Excel в таблицу, если они ещë не в этом формате. Для работы с данными, вы можете передать в Power Query таблицу данных.
Если вы хотите следовать со мной в этом уроке, я сделал пример данных в этом файле Excel, который доступен бесплатно.
Преобразование в таблицу
Чтобы преобразовать данные в таблицу, кликните в любом месте таблицы, а затем найдите параметр Форматировать как таблицу, это находится на ленте Excel. Вы можете нажать на любую из миниатюр стилей, чтобы преобразовать ваши обычные ячейки в таблицу с даными.
Чтобы преобразовать ваши данные в таблицу, выберите кнопку Форматировать как таблицу на ленте Excel.
Передача в Power Query
Это отправляет данные в новое окно, которое открывается поверх Microsoft Excel. Это совершенно новое окно с множеством функций.
Отмена свертывания данных
На этом этапе вы должны увидеть что-то похожее на снимок экрана ниже. Это окно, в котором находятся инструменты, необходимые для отмены (или сброса данных).
Чтобы отменить свёртывание данных в Excel, выделите все столбцы, которые вы хотите сбросить. В принципе, Power Query преобразует каждый из этих столбцов в самостоятельные строки. Для данного здесь примера, я выделил каждый из столбцов с типом проекта:
Выделите все столбцы, которые вы хотите развернуть в строки, затем щелкните Отменить свёртывание столбцов, чуть повыше ваших данных.
Как только вы нажмете на Отменить свёртывание столбцов, Excel преобразует ваши данные из столбцов в строки. Каждая строка является самостоятельной записью, готовой перейти в сводную таблицу или подойдёт для таблицы данных.
Перевернутые данные появляются в строках вместо столбцов.
Эта особенность кажется волшебной. Теперь давайте вернем данные в Microsoft Excel, чтобы начать с ними работать.
Закрыть и загрузить
После того, как вы закроете и загрузите свой запрос, Excel поместит перевёрнутые данные на новый лист и, с правой стороны, покажет запрос, который мы только что построили.
Закрыть и загрузить отправляют данные обратно в Excel и помещает их в электронную таблицу. Вы также увидите окно запросов с правой стороны, которое показывает запрос, который мы только что создали, чтобы преобразовать наши данные.
Обновление запроса
А что если ваши исходные данные изменились? Если в исходные данные добавлено больше строк, вам не нужно повторять весь этот процесс, чтобы перевернуть эти строки.
Вместо повторного создания запроса с начала, вам необходимо использовать параметр Обновить данные. На скриншоте ниже, я добавлю совершенно новую строку с новым клиентом, к исходному источнику данных:
В этом примере, я добавил нового клиента в исходный набор данных, в выделенной строке.
Это произведёт возврат к исходной таблице и повторит шаги запроса. Поскольку мы добавили новые строки в нашу исходную таблицу данных, Power Query добавит эти новые строки в запрос и запустит по ним преобразование.
Это одна из моих любимых функций Power Query. Мы добавили данные в исходный исходный файл, но в один клик обновили и преобразовали новые данные.
Повторите и продолжайте дальнейшее обучение по использованию Microsoft Excel
Из этого урока вы узнали, как легко преобразовать столбцы в строки в Microsoft Excel. Power Query — это гибкий и надежный инструмент для получения и изменения данных на лету. Изучите другие приёмы Microsoft Excel из этих уроков на Envato Tuts+:
Как вы используете Power Query или другие инструменты для отмены свёртывания ваших данных? Если хотите поделиться советом, обязательно сделайте это ниже в комментариях.
Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Также можно просмотреть сведения, используемые для объединения значений в поле значений.
Развертывание и свертывание данных до различных уровней детализацииСводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Развертывание и свертывание уровней в сводной таблице
В сводной таблице выполните одно из указанных ниже действий.
Примечание: Если кнопки развертывания и свертывания не отображаются, см. раздел Отображение и скрытие кнопок развертывания и свертывания в сводной таблице в этой статье.
Дважды щелкните элемент, который нужно развернуть или свернуть.
Щелкните правой кнопкой мыши элемент, выберите команду Развернуть/свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до "<имя поля>".
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до "<имя поля>".
Развертывание и свертывание уровней в сводной диаграмме
В сводной диаграмме щелкните правой кнопкой мыши подпись категории, для которой нужно отобразить или скрыть сведения уровня, выберите команду Развернуть или свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до "<имя поля>".
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до "<имя поля>".
Отображение и скрытие кнопок развертывания и свертывания в сводной таблице
По умолчанию кнопки развертывания и свертывания отображаются, но их можно скрыть (например, при печати отчета). Чтобы можно было использовать эти кнопки для свертывания и развертывания уровней детализации отчета, они должны быть отображены.
В Excel 2016 и Excel 2013: на вкладке Анализ в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2010: на вкладке Параметры в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2007: на вкладке Параметры в группе Показать или скрыть щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
Примечание: Кнопки развертывания и свертывания доступны только для полей, в которых есть данные для детализации.
Отображение и скрытие сведений для поля значений в отчете сводной таблицы
По умолчанию отображение сведений для поля значений в сводной таблице включено. Чтобы защитить эти данные от просмотра другими пользователями, их отображение можно отключить.
Отображение сведений поля значений
В сводной таблице выполните одно из указанных ниже действий.
Щелкните правой кнопкой мыши поле в области значений сводной таблицы и выберите команду Показать детали.
Дважды щелкните поле в области значений сводной таблицы.
Данные, на которых основано поле значений, будут помещены на новый лист.
Скрытие сведений поля значений
Щелкните правой кнопкой мыши ярлычок листа с данными поля значений и выберите команду Скрыть или Удалить.
Отключение и включение параметра отображения сведений поля значений
Щелкните в любом месте сводной таблицы.
На вкладке Параметры или Анализ (в зависимости от используемой версии Excel) ленты в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы откройте вкладку Данные.
В разделе Данные сводной таблицы снимите или установите флажок Разрешить отображение деталей, чтобы отключить или включить этот параметр.
Примечание: Этот параметр недоступен для источника данных OLAP.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
В Power Query можно преобразовать столбцы в пары "атрибут-значение", где столбцы становятся строками.
Диаграмма, на которой показана таблица слева с пустым столбцом и строками, а также значения атрибутов a1, a2 и a3 в качестве заголовков столбцов. Столбец a1 содержит значения v1, v4 и версии 7, столбец a2 содержит значения v2, V5 и V8, а столбец A3 содержит значения v3, V6 и V9. При отмене свертывания столбцов Таблица справа от диаграммы содержит пустые столбцы и строки, столбец атрибутов с девятью строками a1, a2 и A3, повторяющийся три раза, и столбец значений со значениями от v1 до V9.
Например, при наличии следующей таблицы, где столбцы Country и Date создают матрицу значений, сложно анализировать данные масштабируемым способом.
Таблица, содержащая столбец Country, заданный в типе данных text, а также столбцы 6/1/2020, 7/1/2020 и 8/1/2020, заданные в качестве типа данных целого числа. Столбец Country содержит США в строке 1, Канаде в строке 2 и Панама в строке 3.
Вместо этого можно преобразовать таблицу в таблицу с несведенными столбцами, как показано на следующем рисунке. В преобразованной таблице проще использовать дату в качестве атрибута для фильтрации.
Таблица, содержащая столбец Country, заданный как тип данных text, столбец атрибута, заданный как тип данных text, и столбец значений, установленный как тип данных целого числа. Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панама в последних трех строках. Столбец атрибута содержит 6/1/2020 в первых, более и седьмых строках, 7/1/2020 во второй, пятой и восьмой строках и 8/1/2020 в третьей, шестой и девятой строках.
Ключ в этом преобразовании заключается в том, что в таблице имеется набор дат, которые должны быть частью одного столбца. Соответствующее значение для каждой даты и страны должно быть в другом столбце, что позволяет эффективно создавать пару «атрибут-значение».
Power Query всегда будет создавать пару "атрибут-значение" с помощью двух столбцов:
- Attribute: имена заголовков столбцов, для которых было отменено сведение.
- Value: значения, находящееся под каждым из заголовков несведенных столбцов.
В пользовательском интерфейсе есть несколько мест, где можно найти столбцы отмены свертывания. Можно щелкнуть правой кнопкой мыши столбцы, для которых необходимо отменить сведение, или выбрать команду на вкладке Преобразование на ленте.
Можно отменить сведение столбцов из таблицы тремя способами:
- Отмена свертывания столбцов
- Отменить сведение других столбцов
- Отменить сведение только выбранных столбцов
Отмена свертывания столбцов
В описанном выше сценарии необходимо сначала выбрать столбцы, для которых необходимо отменить сведение. При необходимости можно выбрать сочетание клавиш CTRL при выборе необходимого количества столбцов. Для этого сценария необходимо выбрать все столбцы, кроме одного с именем Country (страна). После выбора столбцов щелкните правой кнопкой мыши любой из выбранных столбцов и выберите отменить сведение столбцов.
Результат этой операции даст результат, показанный на следующем рисунке.
Таблица, содержащая столбец Country, заданный как тип данных text, столбец атрибута, заданный как тип данных text, и столбец значений, установленный как тип данных целого числа. Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панама в последних трех строках. Столбец атрибута содержит 6/1/2020 в первых, более и седьмых строках, 7/1/2020 во второй, пятой и восьмой строках и 8/1/2020 в третьей, шестой и девятой строках. Кроме того, в области Параметры запроса выделена запись отменить сведение столбцов, а код языка M отображается в строке формул.
Специальные рекомендации
После создания запроса, описанного выше, представьте, что исходная таблица будет обновлена, как показано на снимке экрана ниже.
Таблица с одинаковыми исходными столбцами Country, 6/1/2020, 7/1/2020 и 8/1/2020 с добавлением столбца 9/1/2020. Столбец Country по-прежнему содержит значения США, Канады и Панама, но в четвертую строку добавляется Великобритания, а Мексика — в пятую.
Обратите внимание, что добавлен новый столбец для даты 9/1/2020 (1 сентября 2020) и две новые строки для стран UK и Мексика.
При обновлении запроса вы заметите, что операция будет выполнена над обновленным столбцом, но не повлияет на столбец, который был изначально не выбран (в данном примере это страна). Это означает, что все новые столбцы, добавленные в исходную таблицу, также будут отменены.
На следующем рисунке показано, как будет выглядеть запрос после обновления с новой обновленной исходной таблицей.
Таблица со столбцами «страна», «атрибут» и «значение». Первые четыре строки столбца Country содержат США, второй четыре строки содержат значение "Канада", третья четыре строки содержат Панама, четыре четверти строки содержат значение "Великобритания", а пятой четыре строки содержат значение "Мексика". Столбец Attribute содержит 6/1/2020, 7/1/2020, 8/1/2020 и 9/1/2020 в первых четырех строках, которые повторяются для каждой страны.
Отменить сведение других столбцов
Можно также выбрать столбцы, которые не нужно разворачивать, и отменить сведение остальных столбцов в таблице. Эта операция заключается в том, где происходит Отмена свертывания других столбцов .
Результат этой операции будет иметь тот же результат, что и тот, что был получен из столбцов отмены свертывания.
Таблица, содержащая столбец Country, заданный как тип данных text, столбец атрибута, заданный как тип данных text, и столбец значений, установленный как тип данных целого числа. Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панама в последних трех строках. Столбец атрибута содержит 6/1/2020 в первых, более и седьмых строках, 7/1/2020 во второй, пятой и восьмой строках и 8/1/2020 в третьей, шестой и девятой строках.
Это преобразование является критически важным для запросов, имеющих неизвестное количество столбцов. Операция отменяет сведение всех столбцов таблицы, кроме выбранных вами. Это идеальное решение, если в процессе обновления источник данных сценария получил новые столбцы дат, так как они будут отобраны и отменены.
Специальные рекомендации
Аналогично операции отмены свертывания столбцов , если запрос обновляется и в источнике данных выбираются дополнительные данные, все столбцы будут отменены, за исключением тех, которые были выбраны ранее.
Чтобы проиллюстрировать это, предположим, что у вас есть новая таблица, как на следующем рисунке.
Таблица со столбцами Country, 6/1/2020, 7/1/2020, 8/1/2020 и 9/1/2020 со всеми столбцами, для которых задан тип данных Text. Столбец Country содержит: сверху вниз, США, Канада, Панама, Великобритания и Мексика.
Можно выбрать столбец Country (страна ), а затем отменить сведение другого столбца, что приведет к следующему результату.
Таблица со столбцами «страна», «атрибут» и «значение». Столбцы Country и Attribute устанавливаются в тип данных Text. Для столбца значение устанавливается тип данных целое значение. Первые четыре строки в столбце Country содержат США, второй четыре строки содержат значение "Канада", третья четыре строки содержат Панама, четыре четверти строки содержат значение "Великобритания", а пятой четыре строки содержат значение "Мексика". Столбец Attribute содержит 6/1/2020, 7/1/2020, 8/1/2020 и 9/1/2020 в первых четырех строках, которые повторяются для каждой страны.
Отменить сведение только выбранных столбцов
Цель этого последнего варианта — только отменить сведение конкретных столбцов из таблицы. Это важно для сценариев, в которых вы работаете с неизвестным количеством столбцов из источника данных и хотите отменить сведение только выбранных столбцов.
Чтобы выполнить эту операцию, выберите столбцы для отмены свертывания, в этом примере — все столбцы, кроме столбца Country (страна ). Затем щелкните правой кнопкой мыши любой из выбранных столбцов и выберите команду Отменить сведение только выбранных столбцов.
Обратите внимание, что эта операция выдаст те же выходные данные, что и в предыдущих примерах.
Таблица, содержащая столбец Country, заданный как тип данных text, столбец атрибута, заданный как тип данных text, и столбец значений, установленный как тип данных целого числа. Столбец Country содержит США в первых трех строках, Канаде в следующих трех строках и Панама в последних трех строках. Столбец атрибута содержит 6/1/2020 в первых, более и седьмых строках, 7/1/2020 во второй, пятой и восьмой строках и 8/1/2020 в третьей, шестой и девятой строках.
Специальные рекомендации
После выполнения обновления, если в таблице-источнике изменяется новый столбец 9/1/2020 и новые строки для Великобритании и Мексики, выходные данные запроса будут отличаться от предыдущих примеров. Предположим, что исходная таблица после обновления изменяется в таблице на следующем рисунке.
Выходные данные запроса будут выглядеть, как на следующем рисунке.
Он выглядит следующим образом, поскольку операция отмены сведения была применена только к столбцам 6/1/2020, 7/1/2020 и 8/1/2020 , поэтому столбец с заголовком 9/1/2020 остается без изменений.
Несмотря на появление новых технологий, Excel как один из инструментов анализа данных, сегодня остается крайне популярным.
Предметом данной статьи будут примеры часто используемых автором функций Excel, которые быстро и просто реализуются в Power Query.
Особенностью функции ВПР является длительность ее расчета, а также отсутствие универсальности.
Рассмотрим пример: требуется заполнить ФИО руководителя в Таблице 1 пользуясь данными Таблицы 2
Формула переноса данных в самом простом случае для строки 3 будет выглядеть следующим образом:
Если же столбец «Руководитель» будет расположен слева от ИНН, то функция ВПР уже неприменима. Здесь должна использоваться более сложная формула, которая сочетает в себе две функции:
Развернем полученные таблицы, выберем нужный столбец из таблицы 3 (в примере – «Руководитель»). Получаем результат:
Аналогично решается задача слияния по множественным критериям, в этом случае нужно указать несколько столбцов для слияния.
Часто требуется выбрать и проанализировать лишь часть текста. Предположим, при анализе выписки по расчетному счету, требуется извлечь из поля «назначение платежа» искомую информацию. Обычно именно это поле является наименее структурированным. Например, для извлечения номера и даты договора из поля
расположенного в ячейке Excel A1, нам потребуется написать такую формулу:
Надстройка Power Query позволит решить ту же задачу без вычислений. Меню: преобразование – столбец «Текст» – разделить столбец – по разделителю – задать необходимый разделитель (в примере – «№»).
Чтобы извлечь только номер договора из поля, выберем меню: преобразование – столбец «Текст» – извлечь – текст между разделителями (в примере – «№» и « от»).
Получим результат 55, изменим тип данных на целое число.
- Преобразование данных из двумерной таблицы в одномерную и обратно.
Допустим, мы имеем такую таблицу:
| ФИО | Январь | Февраль | Март |
| Иванов И.И. | 100 | 400 | 700 |
| Петров П.П. | 200 | 500 | 800 |
| Сидоров С.С. | 300 | 600 | 900 |
Такой вид таблицы не всегда удобен для анализа. Преобразуем его в одномерную таблицу.
Клик – и получаем результат.
Читайте также: