Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Добавление и удаление строк и столбцов в Numbers на Mac

В таблице можно добавлять и удалять строки и столбцы, а также изменять их порядок. Существует три типа строк и столбцов.

В основных строках и столбцах содержатся данные таблицы.

Строки заголовка и столбцы (если они есть) отображаются в верхней левой части таблицы. Цвет их фона обычно отличается от цвета основных рядов. В основном они используются для указания содержимого определенных строк или столбцов. Данные в ячейках заголовка не используются в расчетах, но текст в ячейках заголовка может использоваться в формулах для ссылки на весь столбец или строку. Таблица может иметь до пяти строк заголовка и до пяти столбцов заголовка.

Строки и столбцы заголовка можно заблокировать, чтобы они всегда были видны при прокручивании таблицы.

Итоговые строки (если они есть) отображаются в нижней части таблицы. В таблице может быть максимум пять итоговых строк.

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

В этой статье мы рассмотрим надстройку Power Query в Excel 2010/2013, группу вкладок «Загрузка и преобразование данных» в Excel 2016 и панель «Получить данные» в Power BI Desktop. Термин «Power Query» используется в том же контексте, что и в предыдущих статьях.

При использовании таблиц Power Query иногда требуется получить значение из одной ячейки таблицы. В статье показано, как это сделать с помощью редактора запросов и редактора кода. Также обсуждаются дополнительные возможности, предоставляемые последним. Эта тема частично рассматривается в главе M книги Power Query Криса Вебба, но она уже устарела.

Ссылка на значение ячейки в Редакторе запросов

Ссылка на значение ячейки в Редакторе кода

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– «Измененный тип» – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

Читайте так же:
Как преобразовать десятичное число в двоичное / восьмеричное / шестнадцатеричное число или наоборот в Excel?

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

«Измененный тип»<1>[poleB]

Вернет значение ячейки из второй строки столбца poleB. Аналогично, выражение

«Измененный тип»<0>[poleC]

Возвращает значение 3, соответствующее первой строке столбца FieldC.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #»Измененный тип»<1>[poleB] вернёт то же самое, что и

«Измененный тип»[poleB]

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

Ссылка на отсутствующие строку или столбец

Как можно сделать ссылку на отсутствующий столбец и/или строку? Конечно, мы получим сообщение об ошибке. Давайте запишем наш пример еще раз:

Поскольку оба выражения возвращают ошибку, таблица не содержит столбецD 5-й строки и столбецD.

Однако вместо ошибки вы можете получить значение NULL, используя оператор «?» после ссылки. Например, выражение

«Измененный тип»<1>[poleD]?

Возвращает нуль вместо сообщения об ошибке:

Будьте внимательны. Экспрессия

«Измененный тип»<4>?[poleB]

По-прежнему возвращает ошибку, но не потому, что пятая строка отсутствует, а потому, что ссылка на пятую строку возвращает null, а в ней нет столбца poleB.

Решением может стать изменение порядка ссылок:

«Измененный тип»[poleB]<4>?

Или применив оператор «?» к обоим ссылкам:

«Измененный тип»<4>?[poleB]?

Эффект первичного ключа

Знаете ли вы, что таблицы Power Query могут иметь первичный ключ (например, один или несколько столбцов, значения которых однозначно идентифицируют каждую строку), определяемый самой надстройкой? Нет? Несмотря на это, пользовательский интерфейс, похоже, не указывает на это. Первичный ключ определяется Power Query в нескольких случаях, включая:

  1. Когда данные импортируются из реляционной базы данных, например, SQL Server, и таблица уже имеет первичный ключ.
  2. Когда кнопка Delete Repeats используется для удаления повторяющихся значений из одного или нескольких столбцов, неявно вызывается функция Table.Distinct()
  3. Когда функция Table.AddKey() применяется к таблице
Читайте так же:
Как преобразовать дробную часть в десятичную в Excel?

Рассмотрим следующую таблицу Excel, по сути, такую же, как и выше, но с новым столбцом, уникально идентифицирующим каждую строку.

( Кстати, вы можете использовать Table. Ключи таблицы Power Query можно найти с помощью функции Keys().

После удаления дубликатов повторите операцию с элементом Retail. Результат следующий:

Убедитесь, что вы не пропустили последний шаг! Теперь есть ссылка на первичный ключ вместо прямой ссылки на номер строки.

Замечания напоследок о производительности

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

Не существует общих правил, которые можно было бы предписать; вместо этого вы должны сами попробовать разные варианты.

Как транспонировать / преобразовать столбцы и строки в одну строку?

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

  • Используйте все повторно: добавляйте наиболее используемые или сложные формулы, графики и другие элементы в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; извлечение или удаление частей текста; преобразование чисел и валют в английские слова.
  • Инструменты слияния: объединение нескольких рабочих книг и листов в одну; объединение нескольких ячеек/строк/столбцов без потери данных; объединение строк и повторных сумм.
  • Инструменты разделения: разделение данных на несколько листов на основе значения; из одной рабочей книги в несколько файлов Excel, PDF или CSV; из одного столбца в несколько столбцов.
  • Вставка пропуска скрытых/фильтрованных строк; подсчет и суммирование по цвету фона; массовая отправка персонализированных писем нескольким получателям.
  • Super Filter: создание расширенных схем фильтрации и применение их к любой электронной таблице; сортировка по неделям, дням, периодичности и т.д.
  • Более 300 мощных функций; работает с Office 2007-2019 и 365; поддерживает все языки; легко внедряется в вашем предприятии или организации.
Читайте так же:
Как применить заморозить / разморозить панели сразу к нескольким листам?

стрелка синий правый пузырьТранспонировать / преобразовывать столбцы и строки в одну строку с помощью формулы

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Как показано на следующем снимке экрана, вы можете использовать длинную формулу для изменения диапазона данных в новую строку на новом листе.

doc-convert-range-to-row1

Примените формулу следующим образом:

1 На новом листе в активной книге щелкните по ячейке A1, скопируйте и вставьте эту формулу на новый лист в активной книге: =OFFSET(Sheet1!$A$1,((ROW()-1)*5)+(FLOOR(COLUMN()-1,4)/4))),(COLUMN()-1)-(FLOOR(COLUMN()-1,4))))

Примечание: Sheet1! $ A $ 1 — это ссылки на лист и диапазон, которые вы хотите использовать.

ROW () — 1) * 5 в приведенной выше формуле представляет собой номер строки 5, а ROW () — 1,4) / 4 означает номер 4-го столбца. Вы можете изменить их по своему усмотрению.

2. затем перетащите знак заливки вправо до появления цифры 0. В этом случае все данные в диапазоне были перемещены в одну строку на новом листе. См. скриншот:

doc-convert-range-to-row2

стрелка синий правый пузырьТранспонировать / преобразовывать столбцы и строки в одну строку с кодом VBA

Диапазон данных также можно преобразовать в одну строку с помощью следующего кода на VBA.

1. Нажмите и удерживайте ALT + F11, чтобы открыть окно Microsoft Visual Basic for Applications (Microsoft Visual Basic для приложений).

2. Нажмите Вставка > Модули и вставьте следующий код в поле Модули.

3. Затем нажмите клавишу F5 для запуска кода, появится диалоговое окно, в котором вы можете выбрать диапазон содержимого, которое вы хотите преобразовать в строку, и нажмите Ok, в другом всплывающем диалоговом окне вы можете выбрать одну ячейку для вывода результата. См. скриншоты:

При нажатии кнопки Ok содержимое выбранного диапазона будет преобразовано в строку. См. скриншот:

Читайте так же:
Как преобразовать формат номера телефона в цифры в Excel?

ПримечаниеВы можете заменить OutRng = OutRng.Offset (0, xCols + 0) на любое удобное для вас число.

Например, если вы хотите разбить результаты, соответствующие исходным строкам, на один столбец, вы можете изменить Set OutRng = OutRng.Offset (0, xCols + 0) на Set OutRng = OutRng.Offset (0, xCols + 1), и результаты будут отображены следующим образом

стрелка синий правый пузырьТранспонировать / преобразовывать столбцы и строки в одну строку с помощью Kutools for Excel

Кажется, что оба этих метода немного сложны для нас, новичков в Excel. Поэтому я расскажу вам об удобном инструменте — Kutools for Excel.

Более 300 инструментов Excel включены в Kutools for Excel. Вы получаете бесплатную 30-дневную пробную версию без каких-либо ограничений. Получить сейчас

С помощью утилиты преобразования диапазона Kutools for Excel вы можете быстро преобразовать диапазон в одну строку, выполнив следующее

1. выберите область, которую необходимо транспонировать.

2. Нажмите Кутулс > Конвертер диапазонов > Диапазон преобразования, см. снимок экрана:

doc-convert-range-to-row4

3. В диалоговом окне Преобразование диапазона выберите опцию Диапазон в одну строку, как показано на скриншоте:

doc-convert-range-to-row5

4. Затем нажмите OK и щелкните ячейку, чтобы отобразить результат во всплывающем окне.

doc-convert-range-to-row6

5. Нажмите OK, и данные диапазона будут перемещены в строку. Смотрите скриншоты :

doc-convert-range-to-row1
-1
doc-convert-range-to-row7

Если вы хотите узнать больше об этой функции, пожалуйста, перейдите на страницу области конверсии.

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

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

Этот вопрос решается аналогичным образом. Например, чтобы закрепить первый столбец таблицы Excel, перейдите на вкладку «Вид», нажмите «Закрепить области» и выберите опцию «Закрепить первый столбец».

как закрепить один столбец

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

Читайте так же:
Как проверить, больше ли время или меньше определенного времени в Excel?

линия отделяющая закрепленный столбец

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

Преобразование строк в несколько столбцов с помощью оператора Pivot

Вот как преобразовать несколько строк в несколько столбцов:

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

Преобразование строк в несколько столбцов с помощью оператора Pivot

Строки можно преобразовать в несколько столбцов, применив два оператора (UNPIVOT и PIVOT).

  • Используйте оператор UNPIVOT для извлечения значений из столбцов rating, nofuser и avgr и преобразования их в один столбец с несколькими рядами:

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

Преобразование строк в несколько столбцов с помощью оператора Pivot - 2

Этот оператор применяется к результату как шаг для преобразования результирующего столбца в столбец из нескольких строк.

  • Получайте агрегированные данные с помощью оператора pivot и преобразуйте несколько полей в несколько столбцов:

Несколько полей показаны на следующей схеме в виде нескольких столбцов:

Рейтинги фильмов и их пользователей, представленные графически в MS Excel:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector