что такое сводная таблица в гугл таблицах
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как вставить сводную таблицу в Google Таблицы
Таблицы — отличный способ работы с данными. Они помогают упорядочить данные, чтобы вы могли получить из них полезную информацию. Google Таблицы предоставляет дополнительные возможности сводных таблиц, которые делают процесс анализа данных еще более мощным.
Если вы новичок в сводных таблицах, это руководство даст вам толчок, необходимый для того, чтобы глубже вникнуть в свои данные и совершить серьезную магию обработки данных.
Что такое сводные таблицы?
Сводная таблица принимает большой набор данных и помогает подвести итоги и сделать из них выводы.
Когда у вас есть небольшая электронная таблица с небольшим количеством данных, ее легко читать, понимать и делать выводы. Но по мере того, как данные становятся больше, все, что вы видите, — это множество цифр. Тогда становится трудно делать выводы из ваших данных.
Давайте посмотрим на пример использования. Рассмотрим пример данных на изображении ниже. Он состоит из подробностей о продажах, совершенных сотрудниками воображаемой сети магазинов канцелярских товаров. На первый взгляд все, что мы видим, — это множество чисел, дат и текста. Из этих данных довольно сложно понять какой-то смысл.
Но с помощью сводных таблиц вы можете сузить такой большой набор данных и довольно легко визуализировать отношения между точками данных. Например, вы можете использовать сводную таблицу, чтобы ответить на такие вопросы, как:
Теперь, конечно, можно анализировать данные с помощью формул и функций, но сводные таблицы могут сделать вашу работу намного быстрее и проще и с минимальным количеством человеческих ошибок.
Преимущества сводных таблиц в Google Таблицах
Вот некоторые из причин, по которым все больше и больше людей любят сводные таблицы:
В приведенном выше случае использования мы можем легко использовать сводные таблицы, чтобы сузить данные, чтобы показать нам, сколько всего продал каждый торговый представитель.
Как вставить сводные таблицы в Google Таблицы
Вот как вы можете вставлять и использовать сводные таблицы в Google Таблицах для анализа данных в нашем случае использования. Мы получили эти образцы данных со следующей страницы:
Данные можно использовать и загружать бесплатно, поэтому вы также можете загрузить их и преобразовать в Google Таблицы, чтобы следовать нашему руководству.
Вы увидите, что ваша сводная таблица создана в новой электронной таблице с именем Pivot Table 1. Вы можете продолжить и переименовать ее во что-нибудь другое, если вам нужно.
Сводные таблицы, предлагаемые Google Таблицами
Если вы нажмете на любое из предложенных предложений, Google Таблицы автоматически создадут вашу начальную сводную таблицу. Давайте сначала рассмотрим некоторые из этих предложений.
Одно из предложений — это сводная таблица, в которой показано среднее количество единиц, проданных каждым торговым представителем. Если вы нажмете на эту опцию, вы получите сводную таблицу, которая поможет вам легко узнать, какой торговый представитель в среднем продал больше всего единиц, как каждый из них складывается друг против друга и кто, возможно, заслуживает повышения в этом году!
Другое предложение — это сумма удельной стоимости для каждого региона. Это даст вам таблицу с каждым регионом и соответствующей общей стоимостью единицы. Благодаря этому вы можете получить некоторое представление о том, как обстоят дела в каждом регионе (с точки зрения продаж).
Обратите внимание, что вы можете использовать предлагаемую сводную таблицу и добавлять собственные строки и столбцы для ее настройки.
Например, имеет смысл видеть количество проданных единиц по регионам, а не общую стоимость единицы. Таким образом, вы можете удалить столбец «Стоимость единицы», выбрав крестик, соответствующий параметру «Стоимость единицы» в разделе «Значения».
Это приведет к удалению столбца SUM of Unit Costs из сводной таблицы.
Затем вы можете нажать кнопку «Добавить» рядом с полем «Значения» и выбрать «Единицы» из раскрывающегося списка.
Создание собственной настраиваемой сводной таблицы
Если предложения Google Таблиц не соответствуют вашим требованиям, вы также можете вручную создать свою собственную сводную таблицу Google.
Редактор сводной таблицы (на правой боковой панели) также дает вам возможность вставлять данные в сводную таблицу в соответствии с вашими требованиями.
Здесь вы увидите 4 варианта вставки:
Попробуем создать собственную сводную таблицу для выполнения такого анализа:
Сколько продаж сделал каждый торговый представитель по разным типам товаров, проданных в 2018 году?
Чтобы ответить на поставленный выше вопрос, вот что нам нужно в каждом из измерений:
Теперь приступим к созданию нашей сводной таблицы.
Вставка строк:
Чтобы отобразить имена торговых представителей в каждой строке, нажмите кнопку «Добавить» рядом с «Строки» и выберите «Представитель» в раскрывающемся списке. Сводная таблица извлечет имена всех торговых представителей из исходной таблицы.
Обратите внимание, что сводная таблица принимает имена торговых представителей, удаляет дубликаты и отображает их в алфавитном порядке в столбце A.
Вставка столбцов:
Затем мы хотим отобразить разные товары, проданные в каждом столбце. Для этого нажмите кнопку «Добавить» рядом с «Столбцы» и выберите «Элемент» из раскрывающегося списка. В сводной таблице в каждом столбце будут отображаться названия различных элементов без каких-либо повторов.
Итак, теперь ваш стол начинает обретать форму. У вас есть торговые представители по строкам, а типы предметов — по столбцам.
Вставка значений
Чтобы заполнить отдельные ячейки сводных таблиц, нам нужны значения общих продаж, выполненных каждым торговым представителем для каждого элемента.
Нажмите кнопку «Добавить» рядом с полем «Значения» и выберите «Всего» из раскрывающегося списка.
Теперь вы увидите общий объем продаж каждого типа товара для данного торгового представителя.
Если вместо общих продаж вы хотите увидеть средние продажи, щелкните раскрывающееся меню под «Суммировать по» в разделе «Всего» и выберите «Среднее» из раскрывающегося списка.
Итоги в каждой ячейке теперь должны быть заменены средними продажами, сделанными для каждого типа товаров для данного торгового представителя.
Вы также найдете «Общий итог», рассчитываемый автоматически для каждого элемента, в нижней части сводной таблицы. Общая сумма для каждого торгового представителя отображается в правом столбце таблицы.
Добавление фильтров
Наша сводная таблица уже начала давать много полезной информации. Осталось только убедиться, что мы получим средние продажи только за 2018 год. Для этого мы используем фильтры.
Это гарантирует, что средние значения будут учитывать только все даты до 1 января 2019 года (а это только все даты 2018 года!)
Вот и все!
Теперь у нас есть сводная таблица, которая отвечает на наш вопрос: сколько продаж сделал каждый торговый представитель для разных типов товаров, проданных в 2018 году?
Выводы
Сводные таблицы значительно упрощают работу, когда мы хотим проанализировать большие объемы данных или увидеть общую картину. Имея всю необходимую информацию прямо перед собой, теперь мы можем ответить практически на любые вопросы, которые у нас есть о наших данных.
Итак, вперед, попробуйте, откройте для себя всю мощь сводных таблиц Google и сделайте неограниченное количество открытий о своих данных. Теперь вы должны иметь возможность создавать широкий спектр сводных таблиц, от простых до сложных сводных таблиц в Google Таблицах.
Как создать сводную Google таблицу
У вас так много данных, что от множества строк и колонок с данными разбегаются глаза? Вы тонете в море цифр и не можете понять, что происходит? Предположим, вы работаете в компании, занимающейся продажей шоколада в нескольких регионах и работающей с множеством покупателей.
Ваш босс поставил вам задачу – вам необходимо на основе данных о продажах определить лучшего покупателя, самый популярный продукт и наиболее выгодный регион сбыта.
Не спешите впадать в панику и вспоминать, как правильно использовать такие традиционные функции как СЧЕТЕСЛИ, СУММЕСЛИ и подобные им. Расслабьтесь, сделайте глубокий вдох. Эта задача весьма просто решается при помощи сводных таблиц Google.
Что такое Сводные таблицы Google Sheets?
Сводная таблица поможет представить ваши данные в удобном и презентабельном виде.
Главное удобство сводных таблиц состоит в том, что поля в них можно интерактивно перемещать, а данные в них можно фильтровать, группировать, сортировать, рассчитывать итоги и средние значения. Вы можете менять строки и столбцы местами, изменять уровни детализации представления. Это позволяет не только менять внешний вид таблицы, но и взглянуть на ваши данные под совсем другим углом.
Важно также и то, что при этом ваши исходные данные не меняются – что бы вы не делали с вашей сводной таблицей. Вы просто выбираете такой способ их отображения, который позволит вам увидеть новые закономерности и связи. Ваши данные в сводной таблице будут разделены на группы, а огромный объем информации будет представлен в понятной и доступной для анализа форме.
Краткое руководство по сводным таблицам в Google таблицах.
Вот как, к примеру, выглядят данные о ваших продажах, хранящиеся в Google таблице.
Откроем таблицу, в которой содержатся наши исходные данные по продажам. Важно, чтобы данные, которые вы будете использовать, были расположены по столбцам. Каждый столбец представляет собой определённый набор данных. И каждый столбец должен иметь заголовок. Кроме того, в ваших исходных данных не должно быть объединённых ячеек.
Выделите мышкой все ваши данные, которые вы будете использовать для создания сводной таблицы.
В нашем примере вы можете просто выделить столбцы A:G.
Перейдите к новому созданному листу со сводной таблицей. Пока на нём нет данных, но справа появилось меню «Редактор отчетов». При помощи этого меню можно добавить поля в «Строки», «Столбцы», «Значения» и «Фильтр».
Теперь просто нажмите на «Добавить поле» и выберите те данные, которые необходимы для строк и столбцов сводной таблицы, а также для её значений.
К примеру, рассчитаем продажи различных видов шоколада в разных городах.
В поле “Значения” можно настроить тип рассчитываемого итога. Это может быть общая сумма, минимальная или максимальная сумма, средняя стоимость, количество значений и другие варианты. Мы используем для примера общую сумму.
Рекомендуем обратить еще внимание на находящееся в самом низу поле “Фильтр”. Оно позволяет ограничить подсчитываемые данные каким-либо условием. К примеру, рассчитаем итоги продаж только за определённую дату.
Сводная таблица позволяет показать и более сложные группировки данных. Для этого в поле “Строки” или “Столбцы” нужно добавить несколько полей. Вот, например, как можно рассчитать продажи и по городам, и по регионам одновременно.
Важное замечание: в отличие от Excel, сводные таблицы в Google обновляются автоматически. Но мы советуем регулярно проверять, ссылается ли сводная таблица на весь диапазон исходных данных.
Как создать сводную таблицу Google из нескольких диапазонов данных.
Часто случается, что необходимые для анализа данные находятся в разных таблицах. А сводную таблицу можно построить только по одному диапазону данных. Данные сразу из нескольких таблиц при построении сводной таблицы использовать нельзя. Каков же выход?
Для того, чтобы использовать несколько листов в одной сводной таблице, их сначала нужно объединить в одну общую таблицу.
Для такого объединения листов существуют различные решения. Но, учитывая простоту и доступность, мы хотели бы предложить вам использовать следующее дополнение к Google таблицам, которое поможет вам.
Итак, наша сводная таблица готова.
Используем сводную таблицу для анализа данных.
Как видите, даже на самом простом уровне сводная таблица уже позволяет ответить на достаточно важные вопросы.
Однако, вернёмся к вопросам, которые интересуют вашего босса: “Кто мой лучший покупатель?”
Действуя старым и проверенным способом, изменяем строки и столбцы нашей таблицы и получаем искомый результат. Не забудьте отсортировать покупателей с использованием поля “Сумма”.
Аналогичным образом дадим ответы и на другие интересные вопросы. Например, какой товар лучше всего продаётся?
Где мои товары лучше всего продаются?
Где какой товар лучше продаётся?
Итак, в течение 5 минут вы получили при помощи Google таблиц ответы на интересующие вас вопросы. Ваш босс доволен!
Обратите внимание на то, что общий объём продаж одинаков во всех наших сводных таблицах. Это потому, что каждая из них просто представляет одни и те же данные разными способами.
Как создать диаграмму в сводной Google таблице.
Ещё более наглядными становятся наши данные при использовании графиков и диаграмм. Добавить диаграмму к сводной таблице можно двумя способами.
Вот что можно получить по умолчанию из данных нашей последней таблицы, где мы анализировали, какой товар где лучше продаётся.
Есть и второй способ создать диаграмму.
В Google таблицах есть дополнительный сервис, которого ранее не было ни в одной из программ работы с таблицами. В правом нижнем углу есть кнопка “Анализ данных”. Нажимая на нее, вы можете получить различные подсказки и предложения по улучшению представления ваших данных, их анализу, в том числе и созданию диаграмм.
Нажмите значок в правом нижнем углу, и вы сможете не только выбрать самую подходящую диаграмму из нескольких предложенных, но и изменить оформление вашей сводной таблицы.
Вернувшись к тем вопросам, которые мы ранее задавали, получаем диаграмму, которая показывает не только объём покупок каждым из покупателей, но и даёт представление о том, какой именно шоколад предпочитает покупатель.
Обратите внимание, что система анализирует ваши данные и даже отмечает отдельные закономерности, на которые вы сами, возможно, не обратили бы и внимания.
Теперь осталось только отредактировать нашу диаграмму, придать ей наиболее наглядный и читаемый вид, оформить подписи осей и данных. Как это делать – об этом мы поговорим в отдельном материале чуть позже.
Как опубликовать сводную Google таблицу или диаграмму в Интернете.
Если мы выберем лист нашего файла Google таблиц, то мы можем представить его либо как веб-страницу, либо как статический документ в формате PDF. Если мы хотим опубликовать только диаграмму, то варианта также два. Либо это будет интерактивная диаграмма, которая будет изменяться вместе с изменением исходных данных, либо можно просто ограничиться картинкой, показывающей как бы снимок диаграммы в определённый момент времени.
Как видите, вы можете опубликовать свою диаграмму двумя способами. Либо вы используете закладку “Ссылка”, и тогда для публикации используется интернет-ссылка. Этой ссылкой вы можете поделиться любым удобным для вас способом (к примеру, опубликовать ее в соцсетях либо отправить по электронной почте). Второй способ – вы получаете код для встраивания вашей диаграммы непосредственно в код вашего интернет-сайта (если таковой имеется), и все желающие смогут её там посмотреть.
Естественно, любой вариант публикации предусматривает только режим просмотра, ни о какой совместной работе над диаграммой или внесении в нее изменений вашими коллегами речь не идет. Для этого используется совместный доступ к файлу, о котором мы подробно рассказывали.
При помощи этого же меню можно отменить публикацию.
Надеюсь, этот небольшой обзор возможностей сводных таблиц Google вдохновит вас на их использование с вашими собственными данными. Поверьте, вы сразу поймёте, как это легко и удобно. Сводные таблицы помогут вам сэкономить силы и время. Важным для вас будет и то, что созданный сегодня отчет можно легко обновить и использовать в будущем с новыми данными.
Советы по эффективной работе с Google таблицами:
Сводные таблицы часть 1 (GS20)
Привет, дорогие чатлане!
Сегодня поговорим о сводных таблицах. Можно сказать что это вторая веха в освоении таблиц после ВПРа и про них чаще всего спрашивают на собеседованиях.
Итак, прежде чем приступить к созданию сводов, нужно озаботиться правильной шапкой таблицы.
Покажу песочницу, которую буду использовать для демонстрации:
Итак, как создать свод?
Выделяем нужный диапазон, выбираем пункт меню Данные // Сводная таблица (в экселе аналогично в меню Вставить // Insert).
Конструкторы сводов отличаются для ГТ и экселя и дальше буду рассказывать только для ГТ.
Для наглядности буду собирать свод на этом же листе:
Сделаю пример и расскажу о том что это и зачем:
Можно создать еще большую детализацию:
Немного детальнее про конструктор:
И про значения на пересечениях:
Здесь представлены все варианты того, что можно сделать со значениями на пересечениях (просуммировать, посчитать количество, посчитать среднее, максимальное, отклонение, произведение). Отдельно оставлю ссылку на документацию для вариантов VAR и VARP: https://support.microsoft.com/en-us/office/var-varp-functions-e08f5f59-22f3-43d1-863f-a195df09904f#:
MS, Libreoffice & Google docs
470 постов 12.8K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Автор, спасибо за полезность!
Веду таблицу учета доходов на фрилансе. Типа (Дата, Приход, Заказ, Тип заказа).
Теперь удобно смотреть свод суммы за каждый месяц в зависимости от типа заказа. И в процентном соотношении тоже. Магия))
Шо делать, если не умеешь в впр, а предпочитаешь индекс метч делать?)
Ответ на пост «Как из Полного Имени Сделать Фамилию и Инициалы в Excel формулой»
Видео из оригинального поста довольно шакальное и с весьма устаревшей информацией и довольно сложный способ. Обратите внимание на стандартную функцию «Мгновенное заполнение». по этой теме уже был пост от @Veseliy.4el, Мгновенное заполнение в Excel
15 приемов и горячих клавиш Excel и Гугл Таблиц
Работа в Excel и Google таблицах имеет много общего: ячейки, формулы, данные. Формат работы во всех электронных таблицах похож, везде есть приемы и трюки, упрощающие жизнь начинающим и продвинутым пользователям. В этом уроке показаны самые часто используемые приемы работы и горячие клавиши икселя и гугл таблиц, а также, как вставить excel таблицу в гугл и наоборот. После такого простого обучения вам гораздо реже придется гуглить Как в excel закрепить строку или Как вставить таблицу в гугл таблицу)
Таймкоды помогут быстро найти нужный момент:
Формулы Гугл Таблиц, которых нет в Excel
Формулы Google таблиц унаследованы от Excel, но есть и более продвинутые функции: формулы массива, ArrayFormula, текстовые функции и формулы импорта данных из других таблиц (IMPORTRANGE) и сайтов (IMPORTHTML, IMPORTXML). Эти возможности позволяют как работать в гугл таблицах быстрей, так и избежать возможных ошибок (из-за большого количества формул).
10 трюков Google таблиц (и Excel) для продвинутых и начинающих!
Как работать в гугл таблицах и Excel быстро и эффективно. Полезные приемы, которые упростят вам жизнь.
Excel vs Google-таблиц. Что лучше? Сравнение, обзор
Что лучше? Excel или Google-таблицы? Постараемся ответить в этом видео. Сравнить два мощных табличных редактора. Рассмотреть особенности и возможности каждого, разобраться в сильных и слабых местах.
Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения)
Если в Excel, LibreOffice, Google Sheets и т.д. нужно изменить отрицательные числа на положительные, то для этого можно использовать одну универсальную методику с применением вспомогательных столбцов.
Рассмотрим на примере следующей таблицы:
Поэтому формулу нужно поправить. Вписываем следующее:
Прописав эту формулу, протягиваем её на соответствующий таблице диапазон:
В этот раз, как видишь, пустые ячейки, как нам и нужно, остаются пустыми. Поэтому теперь копируем измененные значения, и щелкаем правой кнопкой мышки по верхней левой ячейки целевого диапазона. В появившемся контекстном окне выбираем вариант вставки «Значения»:
Всё готово. Вспомогательные временные столбцы после этого можно удалить.
Это такой универсальные метод работающий, пожалуй, во всех подобных Экселю программах.
В этом видео я также разобрал второй метод, специально для Эксель, который позволяет проделать тоже самое без использования вспомогательных столбцов. Предлагаю посмотреть это видео, так как там попутно также разбираются и другие интересные подтемы:
Планировщик с показателем эффективности в гугл таблицах
У меня появилась потребность в планировщике с диаграммой эффективности, чтобы наглядно видеть сколько задач я выполнил, а сколько ещё осталось. Короче, сразу к делу.
Как вы видите я поставил условия для диапазона А1:С34. В приделах этой области должен находиться ваш планировщик чтобы выполненные задачи зачёркивались, закрашивались. Если форматировать ячейки по формуле «=$A:$A», то вам останется только выбрать цвет фона и зачёркивание.
Ищем место где будет находиться диаграмма эффективности. На её месте вбиваем 2 формулы как на картинке.
Суммеслимн, счётеслимн в гугл таблицах (GS19)
Привет, дорогие чатлане!
Продолжаем цикл мануалов по гугл таблицам.
Сегодня поговорим о функциях ****еслимн. Для чего нужны эти функции?
Для того чтобы из массива данных просуммировать, посчитать, найти среднее, максимальное, минимальное значения по определенным условиям. Функции имеют особенности синтаксиса, о них расскажу ниже.
Как добавить более сложные условия в суммеслимн:
Во второй аргумент мы добавляем формулу массива, которая возвращает нам новый массив, состоящий из набора
Как использовать сравнения в суммеслимн:
Интерфейс гугл таблиц и полезные кнопки (GS11)
Привет, дорогие чатлане!
Второй пост из серии мануала по гугл таблицам. Первый можно найти тут: Вводный пост (GS10)
Т.к. изначальный посыл мануала «прокачать навыки до уровня аналитика», то рассматривать я буду не все кнопки, а только те, которыми лично я постоянно пользуюсь в работе. Это покроет абсолютное большинство бытовых запросов. Отдельно остановлюсь на некоторых функциях, которые лучше избегать и объясню почему.
В меню File нас интересует только одна настройка:
Вот это лучше отключать при работе с большими таблицами:
Это существенно повышает их быстродействие.
Во вкладке Insert сильно больше полезного.
В разделе Image можно провести вставку изображения внутрь ячейки. Относительно новая функция в гугл таблицах, была добавлена около года назад. Если изображение размещено где-то в интернете, то лучше использовать функцию:
Офигительно полезная кнопка. Позволяет копировать формат (стиль ячейки) включая условное форматирование и применять в другом месте таблицы.
Эта часть посвящена формату данных. Убедительно прошу не использовать валюты. Никогда. Часто нужно копировать значения из ячеек и, при таком копировании, копируется и значок валюты и приходится руками это вычищать.
Шрифты и форматирование внешнего вида ячеек.
Правила размещения и переноса текста. Все как в ворде, кроме 3 кнопки. Она позволяет обрезать текст, переносить построчно или продолжать текст за границы ячейки.
Шорткаты для вставки ссылок, комментов и диаграмм. Фильтры же интереснее, о них чуть детальнее:
В чем же прелесть «Черных фильтров»?
2. Их можно называть и сохранять, а список сохраненных фильтров доступен всем.
Отдельно стоит сказать про интерактивную справку:
Она достаточно толковая.
С панелью разобрались, перейдем к разным полезностям.
Как зафиксировать N-первых строк или столбцов?
Либо протянуть ползунки, которые в естественной среде обитания жмутся в угол таблицы:
Вот и все на сегодня, дорогие чатлане. На все вопросы в комментах отвечу. Если есть пожелания или критика- пишите, буду рад почитать. Отдельно хочу поблагодарить @Veseliy.4el, за развитие нашего табличного сообщества и активность в комментариях.