Как транспонировать таблицу в гугл таблицах
Как преобразовать строки в столбцы в Google Таблицах
Измените макет электронной таблицы быстро и легко с помощью функции транспонирования. Вот как преобразовать строки в столбцы в Google Таблицах.
Когда вы создаете электронную таблицу, иногда понимаете, что данные лучше отформатировать по-другому. И хотя приятно знать это до того, как вы начнете вводить свои данные, так бывает не всегда.
Вы можете настроить несколько столбцов только для обнаружения; они лучше подходят в виде рядов или наоборот. Как и функция транспонирования в Microsoft Excel, Google Таблицы предоставляют такую же возможность. Это позволяет легко преобразовывать строки в столбцы или наоборот.
Преобразование строк и столбцов в Google Таблицах
Вы будете использовать тот же процесс в Google Таблицах для преобразования строк в столбцы, как столбцы в строки. Итак, войдите в Таблицы, откройте книгу и выберите лист, который хотите использовать.
После этого вы должны увидеть, как ваши данные перевернуты в новый макет. Для этого практического руководства мы преобразовали строки в столбцы, как показано на скриншоте ниже.
Удалить исходные данные
Теперь, когда вы преобразовали свои строки в столбцы или наоборот, у вас все еще есть исходные данные. Таким образом, даже если вы используете действие «Вырезать» вместо «Копировать» на шаге 2 выше, эти данные останутся. Но вы можете легко удалить его, удалив данные в ячейках или удалив эти столбцы.
В зависимости от того, куда вы вставили транспонированные данные, вы также можете удалить строки. Просто убедитесь, что ваши преобразованные данные не пострадают.
Кончик: Если вы случайно удалили данные, нажмите Отменить в левой части панели инструментов. Вы можете нажимать кнопку непрерывно, чтобы отменить каждое последнее действие по порядку.
Сделайте так, чтобы макет Google Таблиц работал на вас
Вам не нужно зацикливаться на макете, который вы выбираете при первом создании электронной таблицы. С помощью таких полезных функций, как транспонирование, вы можете легко преобразовывать строки и столбцы для наилучшего отображения данных и работы с ними.
По связанным темам при работе с электронными таблицами, посмотрите, как закрепить, скрыть или сгруппировать столбцы и строки или как получить статистику столбцов в Google Таблицах.
Транспонировать Google таблицу и разложить по заказам
Имею таблицу, сформированную из Google Forms:
Хочется сделать из нее что-то типа такого:
Открыл для себя следующую формулу:
Но, она игнорирует все кроме чисел и выводит только всю таблицу целиком, вот так:
А нужно чтобы каждый заказ был отдельно, и друг за другом или еще как-то так, чтобы это удобно было потом распечатать.
1 ответ 1
Подчеркну особо, что эта задача должна решаться скриптами. Из-за отсутствия порядка данных решение формулами остается лишь возможным в теоретическом плане. Практическое применение потребует больших затрат на сопровождение.
Введение
Условно, для трех карточек в которых по три поля
Формулы VLOOKUP и HLOOKUP могут «пробегать» массивы для подстановки по индексам.
Сама задача плохо подходит для формул, поэтому потребуется подготовить данные. Я в примере их просто все привел к строке. Но можно заморочиться.
Решение
Построение заголовков карточек
Построение значений карточек
Итоговая формула
По идее, можно разбить на колонки, чтобы карточек на единицу площади получалось больше.
Вращение таблиц в Google BigQuery (PIVOT)
Сводные таблицы в Excel являются чрезвычайно мощным инструментом, поскольку позволяют быстро изменять форму данных в пару кликов мышки. Например, вам необходимо построить отчёт продаж по магазинам за год, таким образом, что бы в строках были названия магазинов, а в столбцах месяца. Не проблема, кидай в область столбцов сводной таблицы поле месяц, в область строк поле с названием торговых точек, а в поле значение сумму продаж. Отчёт готов.
Содержание
Если вы интересуетесь анализом данных наверняка вам будут интересены мои telegram и youtube канал. Большая часть контента посвящена языку программирования R. Подписывайтесь!
Синтаксис оператора PIVOT
PIVOT вращает таблицу превращая значения одного из полей в отдельные столбцы. Тем кто знаком с понятием Tidy Data, это операция по преобразованию таблицы из длинного формата в широкий.
Пример использования PIVOT
Попробуем проделать эту операцию средствами Google BigQuery Standart SQL.
Код создания приведённой в примере таблицы
PIVOT являеться часть блока FROM и имеет следующий синтаксис:
При использовании PIVOT вам необходимо задать 3 свойства:
Теперь давайте попробуем повернуть таблицу, приведённую в нашем примере.
К сожалению в блоке FOR на данный момент мы не можем использовать подзапрос, названия новых полей необходимо прописывать руками.
Результат запроса:
Результат преобразования через PIVOT
Необязательно оператору FOR передавать список всех уникальных значений разворачиваемого столбца. Например, если нам необходимо вывести продажи только по февралю и марту то мы можем перечислить только их.
Динамическое создание списка столбцов
Выше я писал о том, что вы не можете в блоке FOR ссылаться на какой то конкретный столбец не перечислив руками его значения, из которых будут созданы новые столбцы. Тем не менее один способ есть, и этот способ был подсмотрен в статье Лака Лакшманана «PIVOT in BigQuery».
Если вы запустите этот код, то получите следующий результат:
Теперь мы можем использовать полученную переменную months для генерации запроса.
Полный код генерации и выполнения динамического запроса
Результат:
Мы получили результат аналогичный прежнему, но в данном случае нам не потребовалось в ручную перечислять все месяца в запросе. Когда у вас всего 3 месяца использование такого приёма не оправдано, а если бы у вас были тысячи уникальных значений, то вариант перечисления их в ручную был бы практически невозможен.
Обратная операция UNPIVOT
Оператор UNPIVOT позволяет произвести обратную операцию, хоть в базах данных крайне редко можно встретить таблицы представленные в широком формате.
Синтаксис UNPIVOT:
Код генерации таблицы для примеров:
Таблица которую мы преобразуем из широкого формата в длинный:
Результат данного запроса:
В операторе UNPIVOT мы указали:
название столбца в который мы поместили значения сворачиваемых столбцов sales
название стобца в который поместили имена сворачиваемых столбцов month
Заключение
Standart SQL в Google BigQuery развивается, и на данный момент даёт аналитику весь необходимый инструментарий для реальзации вычислений любой сложности:
PIVOT послужил вишенкой на торте
Если вы интересуетесь анализом данных приглашаю подписать на мой Telegram и YouTube каналы.
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как сделать таблицу в Google Таблицах (с советами по форматированию)
Если вы работаете с данными в Google Таблицах, я могу с уверенностью предположить, что они у вас есть в табличном формате (т.e. Данные в таблице). Хотя вы всегда можете иметь данные в виде простых записей в сетке электронной таблицы в Google Таблицах, если вы можете преобразовать их в хорошо отформатированную таблицу, ваши данные будут намного более читабельными.
В этом руководстве я покажу вам, как быстро преобразовать любые табличные данные в таблицу в Google Таблицах. Я также расскажу о некоторых советах по форматированию, которые помогут вам сделать вашу таблицу намного лучше.
Примечание. Если вы использовали Excel, вы могли знать, что в нем есть функция таблицы Excel. На момент написания этой статьи в Google Таблицах такой функции не было.
Форматирование таблицы в Google Таблицах
Для целей этого руководства давайте рассмотрим приведенную ниже таблицу в Google Таблицах.
Я покажу вам все шаги, которые вы можете предпринять, чтобы быстро преобразовать эту простую на вид таблицу в красивую таблицу в Google Таблицах.
Как только я закончу с форматированием, таблица будет выглядеть намного лучше, как показано ниже.
Итак, давайте начнем с базового форматирования таблиц!
Давайте начнем с некоторых основных приемов форматирования, которые сделают вашу таблицу в Google Таблицах более профессиональной.
Применение границы
Одна вещь, которая моментально изменила бы наши табличные данные, — это применение границ.
Как только вы примените границы, он начинает выглядеть как таблица.
Для этого выберите данные на листе и щелкните значок «Границы» на панели инструментов.
Из появившихся опций выберите цвет границы и примените границу ко всем ячейкам (используя опцию «Вся граница»).
После этого ваши данные будут выглядеть примерно так, как показано ниже:
Выравнивание данных
По умолчанию в Google Таблицах числа выравниваются по правому краю, а текстовые строки выравниваются по левому краю.
Хотя это хорошо работает, в большинстве случаев вам нужно, чтобы ваши заголовки были выровнены по центру.
Даже в вашем наборе данных вы можете захотеть, чтобы некоторые столбцы были выровнены по центру вместо выравнивания по умолчанию.
В нашем наборе данных я выровняю по центру все заголовки и числа в столбце продаж.
Ниже приведены шаги для выравнивания текста заголовка по центру:
Вышеупомянутые шаги выровняли бы по центру весь текст в выбранных ячейках.
Выполните те же действия, чтобы выровнять числа в столбце D по центру, сначала выбрав данные, а затем щелкнув опцию выравнивания по центру на панели инструментов.
После этого ваш набор данных будет выглядеть примерно так, как показано ниже:
Раскрасьте заголовки полужирным шрифтом
Изменив цвет ячеек с заголовком и выделив текст этих ячеек полужирным шрифтом, вы сможете значительно улучшить читаемость ваших данных.
Поскольку заголовки выделены, это обычно первое, на чем обращает внимание читатель. это поможет им понять, что представляет собой таблица и какие данные в ней содержатся.
Ниже приведены шаги по изменению цвета заголовка и форматирования:
После этого ваша таблица должна выглядеть примерно так:
Форматирование чисел
Как видите, ваша таблица в Google Таблицах уже выглядит лучше, чем когда мы начинали.
И последнее, что вы можете сделать при базовом форматировании, — это отформатировать числа, чтобы сделать их более понятными и понятными.
На данный момент у нас есть только цифры в столбце продаж.
Чтобы было лучше, мы можем добавить разделитель тысяч и символ валюты.
Ниже приведены шаги для этого:
После этого ваша таблица должна выглядеть примерно так:
Если вы выполнили все вышеперечисленные шаги, я уверен, вы увидите, что таблица выглядит намного лучше, чем просто числа и текстовые значения в ячейках.
Теперь давайте взглянем на расширенное форматирование, которое вы можете сделать с данными, чтобы сделать их более значимыми и удобочитаемыми для пользователей.
Дополнительно — применение альтернативных цветов к строкам
Одна вещь, которую вы можете сделать, чтобы значительно улучшить читаемость вашей таблицы, — это применить альтернативные оттенки к строкам в данных.
А благодаря тому, что в Google Таблицах каждые несколько дней появляются новые функции, теперь есть встроенный метод для быстрой смены цветов строк в Google Таблицах.
Вот как это сделать:
Как только вы это сделаете, вы заметите, что происходят две вещи: цвет строки заголовка меняется, и чередующиеся строки приобретают немного более темный оттенок, чем остальные.
В то же время вы увидите добавление боли с чередованием цветов, которая открывается в правой части рабочего листа Google Таблиц.
Здесь вы можете указать цвет, который вы хотите для заголовка, и цвет, который вы хотите для чередующихся строк.
После этого ваши данные должны выглядеть примерно так, как показано ниже:
Дополнительно — сортировка столбцов в таблице
Наша цель со всем форматированием — не только украсить его, но и облегчить пользователю понимание данных.
И одна из простых вещей, которая действительно может помочь пользователю при просмотре больших наборов данных, — это сортировка данных по одному или нескольким столбцам.
В нашем примере я бы показал вам, как отсортировать данные так, чтобы все регионы были представлены вместе, а затем в каждом регионе выполнялся поиск данных по стоимости продаж.
Посмотрим, как это сделать:
Вышеупомянутые шаги сортируют ваши данные по двум столбцам — сначала по региону, а затем по продажам в этом регионе.
Это помогает нам получить все данные для одного конкретного региона как группы и показать продажи в нисходящем порядке для этого региона.
Поэтому, если ваш менеджер просматривает этот отчет, и его интересуют только данные по Европе, ей не нужно просматривать весь отчет. она может легко определить кластер для Европы и просмотреть данные для него.
Совет от профессионала: если есть вероятность, что вам может понадобиться вернуть исходный набор данных, рекомендуется создать резервную копию данных.
Расширенный — выделение высоких / низких значений
При работе с числами одна вещь, которая может помочь пользователю быстро просмотреть ваши данные, — это выделить важные точки данных.
В нашем примере пользователю было бы полезно, если бы мы использовали верхнее и нижнее значение или несколько верхних / нижних значений.
Позвольте мне показать вам, как выделить три верхних и три нижних значения в столбце продаж с помощью условного форматирования.
Ниже приведены шаги, чтобы выделить 3 верхних значения:
Вышеупомянутые шаги позволят выделить три основных значения продаж в нашей таблице в Google Таблицах.
Теперь, если вы также хотите выделить три нижних значения, все те же шаги, описанные выше, и вместо этого использовать эту формулу (и выберите параметр «Меньше или равно» на шаге 5):
Кроме того, чтобы отличить его от трех верхних значений, не забудьте выбрать другой цвет (возможно, красный).
После этого ваша таблица будет выглядеть примерно так, как показано ниже:
Точно так же, как мы выделили три верхних из трех нижних значений в таблице, вы также можете попробовать следующее, чтобы сделать вашу таблицу более читаемой:
Удалить форматирование из таблицы в Google Таблицах
Если вам не нравится какое-либо форматирование, которое вы применили к таблице в Google Таблицах, вы также можете легко удалить их.
В большинстве случаев способ удалить формат — это просто перейти к той же опции формата. Скорее всего, есть способ удалить формат.
А если вы хотите удалить все форматирование из таблицы и начать заново, выполните следующие действия:
Вышеупомянутые шаги удаляют все форматирование в таблице в Google Таблицах.
Вот некоторые из вещей, которые вы можете сделать, чтобы преобразовать ваши скромные данные в красивую таблицу в Google Таблицах.
Вам не нужно использовать все методы, которые я рассмотрел в этом руководстве. не стесняйтесь выбирать те, которые соответствуют вашим потребностям.
И, конечно же, по мере того, как вы больше работаете с таблицами Google, вы можете придумать свой собственный стиль форматирования, чтобы создать таблицу в Google Таблицах.
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Сортировка по горизонтали в Google Таблицах (сортировка столбцов слева направо)
В большинстве случаев данные располагаются вертикально (т. е. Вы читаете их сверху вниз), и в Google Таблицах есть встроенная функция для сортировки данных, расположенных вертикально.
Но что, если вы хотите отсортировать данные, расположенные по горизонтали, то есть для чтения слева направо (как показано ниже):
К сожалению, в Google Таблицах нет встроенной функции для горизонтальной сортировки данных (т. е. Сортировки по столбцам вместо строк).
Но есть способы сделать это.
В этом уроке я покажу вам, как сортировать данные в Google Таблицах по горизонтали (используя простую формулу в качестве метода вставки).
Сортировка по горизонтали с помощью функции SORT (СОРТИРОВКИ)
Вы можете использовать волшебство формул, чтобы отсортировать данные по горизонтали.
Хотя функция SORT сама по себе может сортировать только данные, расположенные по горизонтали, мы можем объединить ее с функцией TRANSPOSE, чтобы преобразовать горизонтальные данные в вертикальные.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите отсортировать эти данные на основе оценок учащихся.
Приведенная ниже формула сортирует данные в Google Таблицах по горизонтали:
В приведенной выше формуле используется функция TRANSPOSE, чтобы сначала перенести данные из горизонтального положения в вертикальное. Затем он использует функцию SORT для сортировки этих данных на основе второй строки (которая становится вторым столбцом в транспонированных данных).
Результат функции SORT снова передается в функцию TRANSPOSE, чтобы получить обратно отсортированные горизонтальные данные.
Преимущество использования формулы состоит в том, что она делает полученные данные динамическими.
Это означает, что если вы измените какое-либо значение в исходных данных, он автоматически откорректирует результат, чтобы отобразить только что отсортированные данные.
При использовании формулы для горизонтальной сортировки в Google Таблицах следует помнить о некоторых важных вещах:
Сортировка по горизонтали путем переноса данных вручную
Если вам не нужны исходные данные, вы также можете вручную перенести данные, отсортировать их (точно так же, как вы сортируете любой вертикальный диапазон ячеек), а затем транспонировать обратно, чтобы получить отсортированные горизонтальные данные.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите отсортировать эти данные на основе оценок учащихся.
Ниже приведены шаги для сортировки этих данных по горизонтали:
Вышеупомянутые шаги предоставят вам транспонированные данные (как показано ниже), которые вы можете легко отсортировать, используя встроенную функцию SORT (СОРТИРОВКИ) в Google Таблицах.
Ниже приведены шаги для сортировки этих транспонированных данных:
Теперь, когда у вас есть отсортированные данные, вы можете снова скопировать и вставить их как транспонированные данные, и это будет то же самое, что и сортировка данных по горизонтали в Google Таблицах.
Итак, это два способа горизонтальной сортировки данных в Google Таблицах.
В большинстве случаев я бы предпочел использовать метод формулы, так как его проще использовать. Если вы не можете использовать формулу, вы можете использовать метод транспонирования вставки для сортировки по горизонтали.
Примечание. Если возможно, попробуйте и посмотрите, можно ли изменить горизонтальные данные на вертикальные. Это помогает в случае, если вам нужно поддерживать и использовать эти данные, поскольку большинство формул и функций в Google Таблицах предназначены для работы с вертикальным набором данных.