Как сделать сводную таблицу в гугл таблице

Как вставить сводную таблицу в Google Таблицы

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

Если вы новичок в сводных таблицах, это руководство даст вам толчок, необходимый для того, чтобы глубже вникнуть в свои данные и совершить серьезную магию обработки данных.

Что такое сводные таблицы?

Сводная таблица принимает большой набор данных и помогает подвести итоги и сделать из них выводы.

Когда у вас есть небольшая электронная таблица с небольшим количеством данных, ее легко читать, понимать и делать выводы. Но по мере того, как данные становятся больше, все, что вы видите, — это множество цифр. Тогда становится трудно делать выводы из ваших данных.

Давайте посмотрим на пример использования. Рассмотрим пример данных на изображении ниже. Он состоит из подробностей о продажах, совершенных сотрудниками воображаемой сети магазинов канцелярских товаров. На первый взгляд все, что мы видим, — это множество чисел, дат и текста. Из этих данных довольно сложно понять какой-то смысл.

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

Какой продавец принес наибольший доход за определенный месяц?
В каком регионе было больше всего продаж?
Сколько штук карандашей было продано?
Какие торговые представители не смогли достичь своих целей?

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

Преимущества сводных таблиц в Google Таблицах

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

Как упоминалось выше, сводная таблица может помочь более эффективно анализировать данные.
Их быстрее составлять и легче исследовать, чем формулы и функции.
Они гибкие и универсальные. Используя сводные таблицы, вы можете сузить круг своих данных до важных точек и добавлять к ним дополнительные данные по мере необходимости.
Они даже позволяют при необходимости суммировать данные, используя агрегаты, формулы и функции.

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

Как вставить сводные таблицы в Google Таблицы

Вот как вы можете вставлять и использовать сводные таблицы в Google Таблицах для анализа данных в нашем случае использования. Мы получили эти образцы данных со следующей страницы:

Данные можно использовать и загружать бесплатно, поэтому вы также можете загрузить их и преобразовать в Google Таблицы, чтобы следовать нашему руководству.

Выделите все ячейки в данной таблице. Вы можете сделать это быстро, выбрав первую ячейку в правом верхнем углу, затем нажав клавишу Shift, одновременно щелкнув последнюю ячейку в правом нижнем углу. Убедитесь, что вы также выбрали заголовки столбцов.
В меню «Данные» выберите «Сводная таблица».
Вас спросят, хотите ли вы вставить сводную таблицу на новый лист или на существующий. Обычно лучше использовать свежий лист. Поэтому выберите вариант «Новый лист».
Нажмите на кнопку «Создать».

Вы увидите, что ваша сводная таблица создана в новой электронной таблице с именем Pivot Table 1. Вы можете продолжить и переименовать ее во что-нибудь другое, если вам нужно.

Справа вы увидите боковую панель, которая представляет собой «Редактор сводной таблицы». Здесь вы найдете несколько сводных таблиц, которые Google Таблицы автоматически предлагает на основе выбранных вами данных. Вы можете либо принять предложения (если вы считаете, что они правильно отвечают на ваши вопросы), либо создать свои собственные сводные таблицы с данными, которые вам требуются.

Сводные таблицы, предлагаемые Google Таблицами

Если вы нажмете на любое из предложенных предложений, Google Таблицы автоматически создадут вашу начальную сводную таблицу. Давайте сначала рассмотрим некоторые из этих предложений.

Одно из предложений — это сводная таблица, в которой показано среднее количество единиц, проданных каждым торговым представителем. Если вы нажмете на эту опцию, вы получите сводную таблицу, которая поможет вам легко узнать, какой торговый представитель в среднем продал больше всего единиц, как каждый из них складывается друг против друга и кто, возможно, заслуживает повышения в этом году!

Другое предложение — это сумма удельной стоимости для каждого региона. Это даст вам таблицу с каждым регионом и соответствующей общей стоимостью единицы. Благодаря этому вы можете получить некоторое представление о том, как обстоят дела в каждом регионе (с точки зрения продаж).

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

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

Это приведет к удалению столбца SUM of Unit Costs из сводной таблицы.

Затем вы можете нажать кнопку «Добавить» рядом с полем «Значения» и выбрать «Единицы» из раскрывающегося списка.

Создание собственной настраиваемой сводной таблицы

Если предложения Google Таблиц не соответствуют вашим требованиям, вы также можете вручную создать свою собственную сводную таблицу Google.

Редактор сводной таблицы (на правой боковой панели) также дает вам возможность вставлять данные в сводную таблицу в соответствии с вашими требованиями.

Здесь вы увидите 4 варианта вставки:

Ряды
Столбцы
Ценности
Фильтры

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

Сколько продаж сделал каждый торговый представитель по разным типам товаров, проданных в 2018 году?

Чтобы ответить на поставленный выше вопрос, вот что нам нужно в каждом из измерений:

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

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

Вставка строк:

Чтобы отобразить имена торговых представителей в каждой строке, нажмите кнопку «Добавить» рядом с «Строки» и выберите «Представитель» в раскрывающемся списке. Сводная таблица извлечет имена всех торговых представителей из исходной таблицы.

Обратите внимание, что сводная таблица принимает имена торговых представителей, удаляет дубликаты и отображает их в алфавитном порядке в столбце A.

Вставка столбцов:

Затем мы хотим отобразить разные товары, проданные в каждом столбце. Для этого нажмите кнопку «Добавить» рядом с «Столбцы» и выберите «Элемент» из раскрывающегося списка. В сводной таблице в каждом столбце будут отображаться названия различных элементов без каких-либо повторов.

Итак, теперь ваш стол начинает обретать форму. У вас есть торговые представители по строкам, а типы предметов — по столбцам.

Вставка значений

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

Нажмите кнопку «Добавить» рядом с полем «Значения» и выберите «Всего» из раскрывающегося списка.

Теперь вы увидите общий объем продаж каждого типа товара для данного торгового представителя.

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

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

Вы также найдете «Общий итог», рассчитываемый автоматически для каждого элемента, в нижней части сводной таблицы. Общая сумма для каждого торгового представителя отображается в правом столбце таблицы.

Добавление фильтров

Наша сводная таблица уже начала давать много полезной информации. Осталось только убедиться, что мы получим средние продажи только за 2018 год. Для этого мы используем фильтры.

Чтобы добавить фильтр, нажмите кнопку «Добавить» рядом с «Фильтрами» и выберите «Дата заказа» из раскрывающегося списка.
В разделе «Дата заказа» щелкните раскрывающееся меню под «Статус».
Нажмите «Фильтр по условию».
В нашей исходной таблице указаны даты только за 2018 и 2019 годы. Поскольку нам нужны значения только за 2018 год,
- Выберите «Дата раньше».
- Под этим вы увидите еще одно раскрывающееся меню рядом со словом «сегодня». Щелкните по нему и выберите «точную дату».
- Введите дату 01.01.2019 в поле ввода.
Это гарантирует, что средние значения будут учитывать только все даты до 1 января 2019 года (а это только все даты 2018 года!)
- Нажмите ОК, чтобы обновить сводную таблицу.
Вот и все!

Теперь у нас есть сводная таблица, которая отвечает на наш вопрос: сколько продаж сделал каждый торговый представитель для разных типов товаров, проданных в 2018 году?

Выводы

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

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

Как использовать сводные таблицы в Таблицах Google (полное руководство)

Andrew Childress Jun 15, 2017

Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)

Сводные таблицы это излюбленная функция уверенных пользователей Excel. Конечно, Excel тяжеловесный чемпион среди электронных таблиц. Но Google Sheets бесплатный web-инструмент, который идеален для совместной работы и обладает множеством собственных возможностей.

Электронная таблица это просто набор строк и столбцов. При пересечении строк и столбцов образуются ячейки. Это то куда вы записываете данные, используя формулы. С ростом таблицы и появлением всё больше и больше записей, вам понадобится инструменты для понимания этих данных.

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

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

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

Как быстро использовать сводные таблицы в Google Sheets (смотри и учись)

В видеоуроке ниже, я помогу вам начать работать с Google Sheets, чтобы вы смогли проанализировать ваши данные. К концу видео, вы узнаете как использовать сводные таблицы по версии Google, для обработки больших объемов данных и их понимания.

Продолжайте читать иллюстрированное, пошаговое руководство по использованию сводных таблиц в Google Sheets. Читайте и следуйте:

Почему сводные таблицы?

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

Сводные таблицы это лишь инструмент, который мы можем использовать для анализа наших данных. Они помогают нам брать большой набор данных и создавать отчеты, которые отвечают на наши вопросы.

Просмотрите на снимок экрана ниже, чтобы увидеть мощь сводных таблиц. Все они связаны с одним и тем же набором данных, но имеют разные представления итоговых данных. Я собрал свою внештатную работу, показав счета моих клиентов, топ-проекты и клиентские счета за год.

Мы используем одни и те же исходные данные, но каждая из этих сводных таблиц, помогает нам увидеть наши данные по-другому; мы можем видеть итоговые суммы проекта слева направо по клиентам, типу проекта и году.

Сводные таблицы берут ваши исходные данные и помогают вам лучше понять их, группируя и подводя итог ваших данных. Давайте узнаем, как использовать сводные таблицы в Google Sheets.

1. Загружаем пример данных

К этому уроку я предлагаю пример набора данных, на которых вы можете учиться, повторяя то, что я делаю. Я создал липовые данные «счетов клиентов» или суммы, на которые мы выставили счета нашим клиентам за фиктивную нештатную работу.

Данные в формате CSV (значения разделены запятой) или необработанные данные, которые вы импортируете в таблицы. Загрузите и распакуйте этот файл, найдите там файл «sample-data.csv«.

Вот как импортировать данные в Таблицы Google:

Шаг 1. Создайте новую таблицу

Заходим на Google Drive и начинаем создание нового файла таблицы. Импортирование лучше начинать с пустого листа.

Шаг 2. Импорт данных в Google Sheets

Найдите пункт меню Файл > Импорт. Укажите на файл sample-data.csv для его импорта.

Шаг 3. Выбираем параметры

Я оставлю опции по умолчанию. Я предпочитаю Создать таблицу для создания отдельного файла и автоматическое обнаружение разделителей. Нажмите Импортировать для завершения.

Когда импортирование будет завершено, то вы увидите сверху ссылку на ваши данные, нажав на которые, вы увидите загруженные данные в таблицу Google.

2. Вставка сводной таблицы в Google Таблицы

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

Выделите столбцы с данными, затем перейдите в Данные > Сводная таблица.

Изучаем редактор отчетов

Теперь мы готовы начать обработку наших данных. Это означает, что мы будем использовать Редактор отчётов Таблиц Google, для построения различных представлений наших данных, чтобы они лучше читались.

Для сводных таблиц берутся большие таблицы исходных данных и размещаются в отчёте так, что вы можете их понимать. Редактор отчётов это инструмент таблиц для построения вашей сводной таблицы и лучшего понимания ваших данных.

Давайте познакомимся с Редактором отчётов. В Таблицах Google есть четыре опции на левой стороне, для размещения данных в сводной таблице в: Строки, Столбцы, Значения, и Фильтр.

Редактор отчётов позволяет вам создавать отчёты и представления данных, таким образом, каким вы хотите их видеть.

Если мы нажмём Добавить поле в любом варианте, вы увидите названия столбцов ваших исходных данных. Нажмите на название столбца, чтобы добавить данные в заданном формате.

В этом и состоит магия сводных таблиц. Вы можете взять любые столбцы своих исходных данных и построить отчёт, размещая поля исходных данных в сводной таблице.

Например, если я нажму Добавить поле рядом со Строки, а затем нажму Client, вы увидите следующее:

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

Вот, что делает каждая из четырёх опций в редакторе отчётов, когда вы добавляете в него поле:
- Строки — добавьте столбец как строку, чтобы увидеть каждое из значений на левой стороне вашей сводной таблицы.
- Столбцы — когда вы добавляете поле как столбец, каждый из элементов будет показан в отдельном столбце.
- Значения — разместите цифровые значения в раздел значений
- Фильтр — вы можете указать поля в разделе полей, что позволит вам отфильтровать ваши данные, например отфильтровать данные по конкретному году.
Посмотрите на скриншот ниже, чтобы увидеть полный отчёт, который я сделал с помощью таблиц Google. Я указал клиентов в строки, типы проектов в столбцы. Я также разместил выставленные счёта в полях значения, чтобы увидеть количество комбинации.

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

3. Анализ данных с помощью сводных таблиц

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

В каждой строке есть несколько ключевых данных о проекте, который мы сделали для клиента:
- Клиент, для которого мы выполняли работу.
- Тип проекта, который мы выполнили каждый раз.
- Дата, когда проект был закончен.
- Потраченные часы; сумма, на которую выставлен счёт; расчётная «почасовая ставка»
Поскольку каждая строка представляет собой комбинацию клиент-проект, трудно обобщить наши данные.

Сводная таблица может помочь нам быстро ответить на вопросы по этими данными:
- Какому клиенту выставить счёт за больше всего оказанных услуг?
- Какой тип проекта имеет самую высокую почасовую ставку?
- Но сколько всего мы выставили счетов в 2016, по сравнению с 2017?
Давайте посмотрим на то, как построить такие сводные таблицы ниже:

Какому клиенту мы выставили больше всего счетов?

Вся идея сводных таблиц заключается в том, что мы можем использовать их для понимания наших таблиц. У нас, конечно, нет времени, чтобы вручную вычислить эти вещи, поэтому сводные таблицы — дадут нам ответ.

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

Давайте разместим клиентов (client) по строкам, а amount billed в поле значения:

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

Какой тип проекта имеет самую высокую почасовую ставку?

На этот раз, давайте заменим клиентов на тип проекта. Так как мы хотим увидеть почасовую ставку за проект, давайте уберём поля, добавленные в первом примере. Вы можете сделать это, нажав на X для каждого поля в редакторе отчёта.

Теперь, давайте разместим project type по строкам, а hourly rate в раздел значений нашего отчёта:

Однако мы не хотим видеть сумму почасовых ставок. Мы предпочли бы видеть средние почасовые ставки.

Нажмите на стрелочку рядом с Суммировать по и выберите AVERAGE. Это изменяет способ отображения сумм; теперь, мы видим среднюю часовую ставку для клиента.

Этот пример является отличной иллюстрацией того, что вы можете просматривать суммы по-разному. Попробуйте другие значения в этом выпадающем списке, например count, max, min и другие.

Выставленные счёта по годам

Теперь давайте разложим наши данные и разделим их по годам.

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

Все эти сводные таблицы привязаны к одному и тому же источнику данных. Построение этих отчетов вообще не заменяет или изменяет исходные данные. Это даёт нам возможность лучше анализировать и просматривать наши данные.

Узнайте больше о Таблицах Google

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

Лучше всего в Таблицах это то, что это особый гибрид между традиционным приложением таблиц и подключенным веб-инструментом. У вас есть приложение мощь приложения таблиц для внесения ваших данных и использования формул. А также у вас инструмент, который подключается к веб-службам и может легко получать данные.

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

Как вы используете сводные таблицы в Таблицах Google? Или есть ли другие инструменты, которые вы используете для анализа данных ваших таблиц? Дайте мне знать в комментариях ниже.

Сводные таблицы часть 1 (GS20) ⁠ ⁠

Сегодня поговорим о сводных таблицах. Можно сказать что это вторая веха в освоении таблиц после ВПРа и про них чаще всего спрашивают на собеседованиях.

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

Пост же я разобью на две части. Первая — собственно своды, вторая — обращение к сводам извне и аналог сводов в функции query().

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

Первое — название каждого столбца должно быть уникальным (это для удобства, иначе в конструкторе будут два одинаковых варианта).

Второе (и это уже обязательно) — в таблице не должно быть объединенных ячеек.

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

Н.Б. Для эксельщиков: ГТ плохо работает с датами в сводах, поэтому месяц пришлось вынести в отдельный столбец, для экселя — это не обязательно.

Итак, как создать свод?

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

Конструкторы сводов отличаются для ГТ и экселя и дальше буду рассказывать только для ГТ.

Для наглядности буду собирать свод на этом же листе:

Красная зона — сама сводная таблица, синяя — конструктор свода.

Сделаю пример и расскажу о том что это и зачем:

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

В моем примере — я взял значения из столбца «Месяц» и сделал их строками новой таблицы. Аналогично — менеджеров я сделал столбцами, а на пересечении — сумма значений столбца Сумма.

Можно создать еще большую детализацию:

Немного детальнее про конструктор:

Здесь можно выбрать порядок сортировки (возрастание или убывание), саму сортировку — алфавитная, значения конкретного столбца свода, показать или скрыть итоги и показать или скрыть названия строк для каждой строки ниже уровнем (скрин ниже):

Если поставить последнюю галочку — в этой ячейке будет повторно записано значение «6».

И про значения на пересечениях:

Здесь представлены все варианты того, что можно сделать со значениями на пересечениях (просуммировать, посчитать количество, посчитать среднее, максимальное, отклонение, произведение). Отдельно оставлю ссылку на документацию для вариантов VAR и VARP: https://support.microsoft.com/en-us/office/var-varp-functions-e08f5f59-22f3-43d1-863f-a195df09904f#:

Если сделать двойной клик по любой ячейке свода — будет создан лист, который покажет все строки оригинальной таблицы, которые были записаны в эту ячейку:

Последнее — фильтры. Они позволяют отсечь те или иные значения из сводов и работает как обычный фильтр в таблицах:

576 постов 13.7K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Автор, спасибо за полезность!
Веду таблицу учета доходов на фрилансе. Типа (Дата, Приход, Заказ, Тип заказа).
Теперь удобно смотреть свод суммы за каждый месяц в зависимости от типа заказа. И в процентном соотношении тоже. Магия))

Шо делать, если не умеешь в впр, а предпочитаешь индекс метч делать?)

Перевести таблицу в список⁠ ⁠

Сломал уже голову, как таблицу перевести в формат строк.

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

Задача перевести таблицу в одномерную таблицу, в которой прописана неделя, клиент, товар, количество к закажу

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

Таблица считает план/факт заказов поставщиков -> затарты сырья на производстве -> поступления сырья -> кассовый разрыв между отсрочкой клиента и закупкой поставщика.

Были пробы сделать понедельно через FILTER, и потом объединять в общий список, но кол-во недель в году расстраивает)

ССылка на табличку (пример) ТУТ

Есть еще второй вопрос, по следующей механики работы, но будет разбираться маленькими шажками.

Ответ на пост «Как из Полного Имени Сделать Фамилию и Инициалы в Excel формулой»⁠ ⁠

Видео из оригинального поста довольно шакальное и с весьма устаревшей информацией и довольно сложный способ. Обратите внимание на стандартную функцию «Мгновенное заполнение». по этой теме уже был пост от @Veseliy.4el, Мгновенное заполнение в Excel

а также ютуб вариант, автор — Николай Павлов

15 приемов и горячих клавиш Excel и Гугл Таблиц⁠ ⁠

Работа в Excel и Google таблицах имеет много общего: ячейки, формулы, данные. Формат работы во всех электронных таблицах похож, везде есть приемы и трюки, упрощающие жизнь начинающим и продвинутым пользователям. В этом уроке показаны самые часто используемые приемы работы и горячие клавиши икселя и гугл таблиц, а также, как вставить excel таблицу в гугл и наоборот. После такого простого обучения вам гораздо реже придется гуглить Как в excel закрепить строку или Как вставить таблицу в гугл таблицу)

Таймкоды помогут быстро найти нужный момент:

00:00 — Введение

00:16 — Горизонтальная прокрутка

00:36 — Автоматическая ширина столбца

00:58 — Перенос текста в ячейке и переход на новую строку

01:30 — Как переместить столбцы и строки

01:49 — Как закрепить строку или столбец

02:08 — Автозаполнение и автоматическая нумерация

02:26 — Формат числа: с разделителями, денежный, дата.

03:01 — Специальная вставка: только значения или формат

03:22 — Автоматический поиск и автозамена

03:55 — Формулы, абсолютные и относительные ссылки

04:57 — Повтор последнего действия

05:18 — Быстрая сумма, среднее, минимум и максимум

05:31 — Фильтры и сортировка

06:11 — Печать

06:28 — Копирования из Excel в Гугл и обратно

Формулы Гугл Таблиц, которых нет в Excel⁠ ⁠

Формулы Google таблиц унаследованы от Excel, но есть и более продвинутые функции: формулы массива, ArrayFormula, текстовые функции и формулы импорта данных из других таблиц (IMPORTRANGE) и сайтов (IMPORTHTML, IMPORTXML). Эти возможности позволяют как работать в гугл таблицах быстрей, так и избежать возможных ошибок (из-за большого количества формул).

0:00 — Приветствие Гугл драйв в деле!

0:16 — Плюсы гугл таблиц

0:35 — Формулы массива UNIQUE FILTER SORT

2:00 — ARRAYFORMULA

3:20 — Текстовые функции JOIN и SPLIT

4:04 — Интеграции и импорт GOOGLETRANSLATE IMAGE GOOGLEFINANCE

4:30 — IMPORTHTML IMPORTXML IMPORTRANGE

10 трюков Google таблиц (и Excel) для продвинутых и начинающих!⁠ ⁠

Как работать в гугл таблицах и Excel быстро и эффективно. Полезные приемы, которые упростят вам жизнь.

0:06 — Как быстро создать таблицу

0:37 — Как быстро найти и открыть таблицы

0:47 — Как открыть Гугл Таблицу в двух окнах

1:00 — Горячие клавиши Гугл Таблиц

1:06 — Как скрыть строки

1:38 — Формат таблиц, чередование цветов

2:05 — Копирование сразу нескольких диапазонов

2:30 — Именованные диапазоны

3:01 — Как показать и проверить формулы в гугл таблицах

3:29 — Справка и обучение в гугл таблицах

Excel vs Google-таблиц. Что лучше? Сравнение, обзор⁠ ⁠

Что лучше? Excel или Google-таблицы? Постараемся ответить в этом видео. Сравнить два мощных табличных редактора. Рассмотреть особенности и возможности каждого, разобраться в сильных и слабых местах.

0:45 — Базовое сравнение Excel 2019 (365) и Google-таблиц

1:33 — Сравнение интерфейсов Excel 2019 (365) и Google-таблиц

3:32 — Что умеют Google-таблицы?

10:39 — Практический пример и сравнение табличных редакторов

17:48 — В чем лучше работать? В Excel или Google-таблицах?

Excel: Как изменить отрицательные числа на положительные и наоборот (минусовые на плюсовые значения)⁠ ⁠

Если в Excel, LibreOffice, Google Sheets и т.д. нужно изменить отрицательные числа на положительные, то для этого можно использовать одну универсальную методику с применением вспомогательных столбцов.

Рассмотрим на примере следующей таблицы:

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

Поэтому формулу нужно поправить. Вписываем следующее:

Прописав эту формулу, протягиваем её на соответствующий таблице диапазон:

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

Всё готово. Вспомогательные временные столбцы после этого можно удалить.

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

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

Планировщик с показателем эффективности в гугл таблицах⁠ ⁠

У меня появилась потребность в планировщике с диаграммой эффективности, чтобы наглядно видеть сколько задач я выполнил, а сколько ещё осталось. Короче, сразу к делу.

Для столбца А. Жмём Вставка — Флажок. Не создавайте флажки на будущее. Это будет отражаться на диаграмме эффективности. Придумали 10 задач на день — 10 строк и создавайте.

Формат — Условное форматирование. Выставляем условия как на картинке.

Как вы видите я поставил условия для диапазона А1:С34. В приделах этой области должен находиться ваш планировщик чтобы выполненные задачи зачёркивались, закрашивались. Если форматировать ячейки по формуле «=$A:$A», то вам останется только выбрать цвет фона и зачёркивание.

Ищем место где будет находиться диаграмма эффективности. На её месте вбиваем 2 формулы как на картинке.

2 ячейки с формулами будут показывать 2 числа (Сколько зачёркнутых и не зачёркнутых строк). Выделяем их и создаём круговую диаграмму. Далее Изменить диаграмму — Дополнительно — Расположение — Не выбрано (чтобы исчезли проценты).

Суммеслимн, счётеслимн в гугл таблицах (GS19)⁠ ⁠

Привет, дорогие чатлане!

Продолжаем цикл мануалов по гугл таблицам.

Сегодня поговорим о функциях ****еслимн. Для чего нужны эти функции?

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

Первый аргумент — столбец который суммируем, второй — столбец для первого условия, третий — первое условие. Все следующие аргументы идут парами.

Для счетеслимн — только столбцы с условиями и сами условия.

Как добавить более сложные условия в суммеслимн:

Во второй аргумент мы добавляем формулу массива, которая возвращает нам новый массив, состоящий из набора и сравниваем его со значением ИСТИНА/TRUE.

Как использовать сравнения в суммеслимн:

Если мы хотим сравнить что-то с датой — дату нужно брать в кавычки:

Отдельно еще расскажу, что лучше всегда использовать именно суммеслимн, а не суммесли, т.к. суммесли имеет непонятным образом отличающийся синтаксис и, используя в работе эти функции и filter() — нужно будет всегда держать в голове какой синтаксис нужно использовать в какой функции, так что лучше одну из них исключить вообще.

Интерфейс гугл таблиц и полезные кнопки (GS11)⁠ ⁠

Привет, дорогие чатлане!

Второй пост из серии мануала по гугл таблицам. Первый можно найти тут: Вводный пост (GS10)

Т.к. изначальный посыл мануала «прокачать навыки до уровня аналитика», то рассматривать я буду не все кнопки, а только те, которыми лично я постоянно пользуюсь в работе. Это покроет абсолютное большинство бытовых запросов. Отдельно остановлюсь на некоторых функциях, которые лучше избегать и объясню почему.

В меню File нас интересует только одна настройка:

Вот это лучше отключать при работе с большими таблицами:

Это существенно повышает их быстродействие.

Во вкладке Insert сильно больше полезного.

В разделе Image можно провести вставку изображения внутрь ячейки. Относительно новая функция в гугл таблицах, была добавлена около года назад. Если изображение размещено где-то в интернете, то лучше использовать функцию:

Tick box — относительно полезная вставка, которая добавляет заполняемые галочки в таблицу. Из плюсов — галочки можно проставлять нажатием на пробел. Из минусов — их сложнее использовать в формулах. На фронте — смело используем, при обработке данных — стараемся избегать.

Комментарии и заметки тоже крайне полезны, однако старайтесь избегать информации в заметки. У нас была относительно плохая практика писать итоговую сумму в ячейку, а в заметку писать из чего она состоит. Это следствие плохой архитектуры таблицы. Чаще всего — смешения ввода и вывода данных.

Из полезного — в комментариях можно назначить исполнителя и ему улетит уведомление на почту. Делается это при помощи +почта. Т.е. внутри комментария ставите плюс, дальше ГТ подгружает контактную книгу и можно выбрать исполнителя.

Остальные кнопки либо вынесены на панель, либо открывают дорогу к более продвинутым функциям (именованные диапазоны и своды), поэтому с менюшками — все, переходим к панели.

Офигительно полезная кнопка. Позволяет копировать формат (стиль ячейки) включая условное форматирование и применять в другом месте таблицы.

Ну и раз уж затронули тему копирования формата — нужно рассказать и про перенос значений:

Комбинация клавиш CTRL+C -> CTRL+SHIFT+V. Переносит ТОЛЬКО содержимое ячеек без формул. Понятие «прибить формулы» — означает выделить таблицу с формулами, нажать эту комбинацию клавиш без переноса. Т.о. все значения сохранятся, в формулы исчезнут. Полезно при больших таблицах, где есть лишние вычисления.

Эта часть посвящена формату данных. Убедительно прошу не использовать валюты. Никогда. Часто нужно копировать значения из ячеек и, при таком копировании, копируется и значок валюты и приходится руками это вычищать.

В остальном же — перевести дату в число или число в процент — это сюда. Про создание кастомных форматом и где это может быть полезно будет отдельный пост.

Шрифты и форматирование внешнего вида ячеек.

В гугл таблицах есть шикарные шрифты, которых нет в Excel. Если любитель — советую покопаться. Lora — ванлав.

Крайняя правая кнопка — объединение ячеек. Стараемся избегать, т.к. таблицы с такими ячейками не хаваются сводными таблицами, а это усложняет нам жизнь.

Правила размещения и переноса текста. Все как в ворде, кроме 3 кнопки. Она позволяет обрезать текст, переносить построчно или продолжать текст за границы ячейки.

Шорткаты для вставки ссылок, комментов и диаграмм. Фильтры же интереснее, о них чуть детальнее:

В ГТ есть два типа фильтров. Один — обычный, как в excel — добавляет в шапку таблицы возможность фильтровать данные. Но вот эта маленькая стрелочка справа открывает доступ к «Черным фильтрам». Разработчики гугла почему-то очень сильно их не любят. Не любят настолько, что это единственный объект в таблице, к которому нельзя обратиться из скриптов. Его тупо нет в документации.

В чем же прелесть «Черных фильтров»?

1 . Они отображаются только у вас, что позволяет нескольким пользователям смотреть разные фильтры.

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

По возможности — советую пользоваться именно ими.

Последняя кнопка — список всех функций.

Отдельно стоит сказать про интерактивную справку:

Она достаточно толковая.

С панелью разобрались, перейдем к разным полезностям.

Как зафиксировать N-первых строк или столбцов?

Классический вариант — через

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

Вот и все на сегодня, дорогие чатлане. На все вопросы в комментах отвечу. Если есть пожелания или критика- пишите, буду рад почитать. Отдельно хочу поблагодарить @Veseliy.4el, за развитие нашего табличного сообщества и активность в комментариях.

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

Сводные таблицы упрощают работу с большими объемами информации и позволяют выявлять взаимосвязи между различными данными. Например, с помощью такой таблицы вы сможете выяснить, кто из продавцов получил наибольший доход за конкретный месяц.

Как добавлять и редактировать сводные таблицы
1. Откройте файл в Google Таблицах на компьютере.
2. Выберите ячейки с исходными данными, которые хотите использовать. Важно! У каждого столбца должен быть заголовок.
3. В верхнем меню нажмите ВставкаСводная таблица. Откройте лист со сводной таблицей (если это не было сделано автоматически).
4. На боковой панели рядом с надписями «Строки» и «Столбцы» нажмите Добавить, а затем выберите значение.
  - Примечание. Иногда вы можете увидеть рекомендуемые сводные таблицы на основе выбранных данных. В этом случае выберите подходящую таблицу в разделе «Рекомендации».
5. На боковой панели рядом с надписью «Значения» нажмите Добавить и выберите значение. . Для этого нажмите на стрелку вниз рядом с нужной настройкой.
Как изменить или удалить данные
1. Откройте файл в Google Таблицах на компьютере.
2. Нажмите кнопку Изменить во всплывающем окне под сводной таблицей.
3. Для изменения или удаления полей используйте боковую панель:
  - Чтобы переместить поле, перетащите его в другую категорию.
  - Чтобы удалить поле, нажмите «Удалить» .
  - Чтобы изменить объем используемых данных, нажмите «Изменить диапазон» .
Примечание. Сводная таблица обновляется каждый раз, когда вы изменяете ячейки с исходными данными.

Вы можете посмотреть исходные данные для ячейки из сводной таблицы.
1. Откройте файл в Google Таблицах на компьютере.
2. Нажмите кнопку Изменить во всплывающем окне под сводной таблицей.
3. Дважды нажмите на нужную ячейку.
4. Откроется новый лист с исходными данными.
Calculated fields with SUM or a custom formula
1. Откройте файл в Google Таблицах на компьютере.
2. Нажмите кнопку Изменить во всплывающем окне под сводной таблицей.
3. На боковой панели рядом с надписью «Значения» нажмите ДобавитьРассчитываемое поле.
  - Вычислите значение с помощью формулы SUM. Для этого рядом с надписью «Суммировать по» нажмите SUM.
  - Вычислите значение с помощью вашей формулы. Для этого в появившемся поле введите формулу. Рядом с надписью «Суммировать по» выберите Другое.
4. В правом нижнем углу нажмите Добавить. Появится новый столбец.
Совет. Для создания собственных формул можно использовать:
- Другие столбцы, например =sum(Цена)/counta(Товар) , где Цена и Товар – столбцы сводной или исходной таблицы (доступны в подключенных таблицах). .
Внимание! Если в формуле есть значение с пробелами, его необходимо заключить в кавычки. Пример: =»h sdf» .
Похожие публикации: