Работа с матрицей в Power View
Важно: В Excel для Microsoft 365 Excel 2021 Power View удаляется 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop,который можно скачать бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.
Матрица — это тип зрительного образа, который похож на таблицы тем, что состоит из строк и столбцов. Однако матрицу можно свертывать и развертывать по строкам или столбцам. Если она содержит иерархию, можно выполнять детализацию и укрупнение. В матрице могут выводиться итоги и промежуточные итоги по столбцам и строкам. Кроме того, в ней можно представлять данные без повторяющихся значений. Ниже приведен пример визуализации данных об олимпийских видах спорта, дисциплинах и соревнованиях.
Слева в таблице перечислены виды спорта и дисциплины для каждого соревнования.
Справа в матрице каждый вид спорта и дисциплина указаны только один раз.
Чтобы создать матрицу, нужно сначала создать таблицу, а затем преобразовать ее в матрицу.
- На вкладке Конструктор в группе Представление переключателя щелкните Таблица > Матрица.
По умолчанию матрица содержит итоги и подытоги для групп, но их можно отключить.
- На вкладке Конструктор щелкните Параметры > Итоги.
Чтобы добавить группы столбцов, перетащите поле в область Группы столбцов.
Совет: Если область Группы столбцов не отображена, на вкладке Конструктор выберите пункт Матрица.
Как из сводной таблицы сделать плоскую в Power Query
Когда мы получаем данные из выгрузки или от коллег, часто возникает проблема со структурой таблицы. Встаёт вопрос: «Как привести данные к нужной структуре, чтоб построить удобный и простой отчёт?».
Рассмотрим как должна выглядеть правильная структура таблицы. Правила будут следующими:
- У каждого столбца должен быть заголовок.
- В каждом столбце данные должны быть однородные, т.е. одного типа. Например, если столбец несет под собой значения даты, то в каждой строке в столбце «Дата» должен быть единый тип.
1. В заголовках имеем диапазон по дате или другим категориям
Чтобы исправить такую таблицу необходимо:
- через CTRL выделить все столбцы с диапазоном, в данном случае кварталы;
- перейти во вкладку «Преобразование»;
- найти кнопку «Отменить свертывания столбцов».
Получаем таблицу, с которой можем дальше проводить анализ в Power BI:
2. В одном столбце неоднородные данные
Бывает, что столбец с названием показателя вынесен отдельно:
Выделим нужный столбец и нажмем на кнопку «Столбец сведения», после чего откроется меню настройки.
Во вкладке «Столбец значений» выбираем значения, которые попадут в новые столбцы. Во вкладке «Функция агрегированного значения» выбираем пункт «Не агрегировать».
По окончании проделанных шагов получаем таблицу на рисунке ниже:
3. Сложная комбинация пунктов 1 и 2.
При комбинации случаев 1 и 2 первым делом необходимо избавиться от пустых значений null. Для этого выберем первый столбец и нажмем «Заполнить значения вниз».
Пустые значения первого столбца пропали, а на их месте теперь название филиала, которое было выше.
Далее перевернем таблицу, для этого нажмем на кнопку «Транспонировать», после выбираем «Заполнить значения вниз», как указано на рисунке ниже:
Следующим шагом избавимся от нескольких заголовков. Для этого необходимо объединить столбцы и нажать на кнопку «Объединить столбцы»:
Столбцы склеиваются в один:
Транспонируем таблицу обратно и используем первую строку в качестве заголовка:
Далее действуем как в предыдущих примерах. Выделяем нужный диапазон и нажимаем «Отменить свертывание столбцов»:
Разделим ранее склеенный столбец, чтобы отделить год.
Выделим столбец, где указаны кол-во и сумма, далее нажмем столбец сведения:
В результате получим простую таблицу, с которой удобно работать и которую легко анализировать:
Наши курсы по Power BI:
Курс Аналитик BI
Курс DAX Mastering
Курс Финансовый анализ в Power BI
покупка
Как преобразовать таблицу стилей матрицы в три столбца в Excel?
Предположим, у вас есть таблица в матричном стиле, которая содержит заголовки столбцов и заголовки строк, и теперь вы хотите преобразовать эту таблицу стилей в таблицу с тремя столбцами, она также называется таблицей списков, как показано на следующем снимке экрана, есть ли у вас какие-либо хорошие способы решить эту проблему проблема в Excel?
Преобразование таблицы стилей матрицы в список с помощью сводной таблицы
В Excel нет прямой возможности преобразовать таблицу стилей матрицы в таблицу с тремя столбцами, но, если вы знакомы со сводной таблицей, она может оказать вам услугу. Пожалуйста, выполните следующие действия:
1. Активируйте свой рабочий лист, который вы хотите использовать, затем удерживая Alt + D, а затем нажмите P в клавиатуре, во всплывающем Мастер сводных таблиц и диаграмм диалоговое окно, выберите Несколько диапазонов консолидации под Где данные, которые вы хотите проанализировать раздел, а затем выберите PivotTable под Какой отчет вы хотите создать раздел, см. снимок экрана:
2. Затем нажмите Следующая кнопку в Шаг 2а из 3 мастера, выберите Я создам поля страницы вариант, см. снимок экрана:
3. Продолжайте нажимать Следующая кнопку в Шаг 2b из 3 мастер, нажмите кнопку, чтобы выбрать диапазон данных, который вы хотите преобразовать, а затем нажмите Добавить кнопку, чтобы добавить диапазон данных в Все диапазоны список, см. снимок экрана:
4, И нажмите Следующая кнопка, в Шаг 3 из 3 мастера, выберите место для сводной таблицы по своему усмотрению.
5. Затем нажмите Завершить кнопка, сводная таблица была создана сразу, см. снимок экрана:
6. В сводной таблице дважды щелкните пересекающуюся ячейку общего итога, в этом случае я дважды щелкните ячейку F22, и она сгенерирует таблицу из трех столбцов, как показано на следующем снимке экрана:
7. И, наконец, вы можете преобразовать формат таблицы в нормальный диапазон, выбрав таблицу, а затем выбрав Настольные > Преобразовать в диапазон из контекстного меню см. снимок экрана:
Преобразование таблицы стилей матрицы в список с кодом VBA
Если вам не нравится первый метод, следующий код VBA также может вам помочь.
1, нажмите Alt + F11 для отображения Microsoft Visual Basic для приложений окно.
2. В окне нажмите Вставить > Модули чтобы отобразить новое окно модуля, затем скопируйте и вставьте следующий код VBA в окно модуля.
Код VBA: преобразование таблицы стилей матрицы в список
Sub ConvertTable() 'Update 20150512 Dim Rng As Range Dim cRng As Range Dim rRng As Range Dim xOutRng As Range xTitleId = "KutoolsforExcel" Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8) Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8) Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8) Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8) Set xWs = Rng.Worksheet k = 1 xColumns = rRng.Column xRow = cRng.Row For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1 For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1 outRng.Cells(k, 1) = xWs.Cells(i, xColumns) outRng.Cells(k, 2) = xWs.Cells(xRow, j) outRng.Cells(k, 3) = xWs.Cells(i, j) k = k + 1 Next j Next i End Sub 3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно, позволяющее выбрать метки столбцов данных, см. снимок экрана:
4, Затем нажмите OK , в следующем окне запроса выберите метки строк, см. снимок экрана:
5. Продолжайте нажимать OK, затем выберите диапазон данных, исключая заголовки столбцов и строк в поле подсказки, см. снимок экрана:
6, Затем нажмите OK, в этом диалоговом окне выберите ячейку, в которой вы хотите разместить результат. Смотрите скриншот:
7, Наконец, нажмите OK, и вы получите сразу таблицу из трех столбцов.
Преобразование таблицы матричного стиля в список с помощью Kutools for Excel
Оба вышеперечисленных метода несколько хлопотны, здесь я познакомлю вас с простым способом — Kutools for Excel, С его Перенести размеры таблицы функция, вы можете быстро преобразовать между матрицей ячеек и таблицей списка.
После установки Kutools for Excel, выполните следующие действия:
1. Нажмите Кутулс > Диапазон > Перенести размеры таблицы, см. снимок экрана:
2. В Перенести размеры таблицы диалоговое окно:
(1.) Выберите Перекрестная таблица в список вариант под Тип транспонирования.
(2.) Затем щелкните под Диапазон источников , чтобы выбрать диапазон данных, который вы хотите преобразовать.
(3.) Затем щелкните под Диапазон результатов чтобы выбрать ячейку, в которую вы хотите поместить результат.
3, Затем нажмите OK кнопку, и вы получите следующий результат, включая исходное форматирование ячейки:
Демонстрация: преобразование таблицы в матричном стиле в список с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Лучшие инструменты для офисной работы
Усовершенствуйте свои навыки работы с Excel с помощью Kutools for Excelи испытайте эффективность, как никогда раньше. Kutools for Excel Предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего.
Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Как создать матрицу из плоской таблицы
• Определение матрицы посредством использования оператора элемента в матрице, диапазона и формулы, связывающей значение элемента массива с его индексами.
• Использование функций, создающих специальные матрицы. Например, функция identity возвращает матрицу n × n , в которой диагональные элементы имеют значение 1, а остальные элементы имеют значение 0.
Создание таблиц
Таблицы могут создаваться одним из двух способов.
• Использование вкладки Матрицы/таблицы (Matrices/Tables) на главной ленте.
• Использование сочетаний клавиш.
Дополнительная информация
• Матрице можно назначить имя переменной и использовать его в любых расчетах.
• Массивы не могут содержать пустые элементы. Вставьте NaN в любой элемент массива, для которого отсутствует вещественное значение.
• Определение элементов по отдельности или вне последовательности может привести к созданию больших матриц или матриц, некоторые элементы которых будут непредвиденно заданы равными 0.