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

Начнем с простой формулы #СУММЕСЛИ (#SUMIF) - это сумма диапазона, соответствующая указанному условию (условие может быть только одно).

Разные примеры применения (в том числе с символьными шаблонами "*" и "?") в Google Документе по ссылке https://goo.gl/JfNHt1

Друзья, привет! Вашему вниманию небольшая памятка с советами, позволяющими ускорить работу документа.

Удалить неиспользуемые строки на каждой вкладке (по умолчанию создается 1000 строк - если у вас на вкладке сейчас используется 200, удалите лишние 800, а при необходимости добавите нужное кол-во) и столбцы (аналогично). Для  этого можно пользоваться надстройкой Crop Sheet - а можно и сделать это вручную;

Оптимизировать количество вкладок (если есть несколько вкладок с маленькими таблицами или списками - попробуйте объединить их в одну);

Если есть формулы поиска данных (ВПР/VLOOKUP, ИНДЕКС/INDEX, ПОИСКПОЗ/MATCH и другие, сохраняйте часть формул как значения (если не нужно будет эти значения обновлять). Например, если у вас подтягиваются данные за много месяцев с помощью VLOOKUP - оставляйте текущий месяц формулами, а остальные данные сохраните как значения;

Не заливать строки/столбцы цветом целиком (и вообще стараться избегать излишнего форматирования);

Проверить, нет ли условного форматирования на большом диапазоне ячеек;

Не ставить фильтр на все столбцы;

Очистить примечания, если их много и они не нужны;

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

P.S. Иногда в документах приходится использовать ресурсоемкие формулы, которые ничем не заменить, например, может потребоваться собирать в один файл данные из 20 разных документов формулой IMPORTRANGE. Если ничего не предпринять, то работа с таким документом может стать мучительной, формулы будут постоянно обновляться и все начнет тормозить.

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

Открытые диапазоны (вида A2:A)
Во многих случаях ваши таблицы будут пополняться и обновляться со временем - путем добавления новых строк.

Если при этом у вас на листе с таблицей нет никаких других данных, кроме заголовков  полей (столбцов) и данных под ними (то есть нет нескольких таблиц, расположенных одна под другой), то есть смысл указывать в аргументах открытые диапазоны вида A2:A, а не A2:A100. Тогда вам не придется каждый раз менять формулы.

Подробнее и со скриншотами - по ссылке:

https://docs.google.com/document/d/1AwvckzQxBbGyNt61RF7yyRRIbXieeXX21yH5SZCB6Tc

Как и в Excel, диапазонам в Таблицах можно присваивать имена. Именованные диапазоны делают формулы наглядными, ведь вместо
=A7*$E$1

вы будете видеть в строке формул что-то вроде:
=Продажи*Налог

Чтобы задать диапазону имя, выделите его и нажмите на кнопку “Именованные диапазоны” в разделе меню “Данные”

Подробности и иллюстрации - по ссылке:

https://docs.google.com/document/d/12FzbMCNUZS03Oys43-KZW8FgJ2JMrfUuUGtOTWmNfvs/

Друзья, внимание! Практически все наши кейсы применимы и для работы в Ms Excel, там будет такой же синтаксис (кроме некоторых формул, о которых мы будем оповещать отдельно)

6 января мы разбирали формулу #СУММЕСЛИ, а сейчас поговорим про #СЧЁТЕСЛИ (COUNTIF).

Это простая формула, позволяющая подсчитать кол-во ячеек в выбранном диапазоне, которые соответствуют нашему условию.

В Google Документе по ссылке разные кейсы с этой формулой, в т.ч. немного нестандартные:
- подсчитаем кол-во непустых ячеек
- кол-во положительных (или отрицательных) чисел
- кол-во адресов, начинающихся на "ул. *"

https://goo.gl/QEVNwB

Друзья, сегодня рассмотрим, как с помощью символа амперсанда (&) в одной ячейке можно объединить:

- результат формулы и какой-нибудь текст
- значения нескольких ячеек, даже из разных документов (при помощи формулы #IMPORTRANGE)

http://goo.gl/rFcxrc

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

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

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

Примечание: в Excel это тоже работает. Но там нужно не только выделить фрагмент формулы, но и нажать F9 после этого. Важно, что потом нужно нажимать Escape, а не Enter, иначе этот фрагмент так и останется числом, а не функцией/диапазоном. В Google Таблицах этого риска нет.

Пример - в гифке. Содержание формулы здесь не играет решающей роли, но ниже будет краткий комментарий по поводу того, что в ней происходит.
http://g.recordit.co/22IjU3BFOS.gif

По ссылке - файл с примером. Копируйте на свой диск, играйтесь, изучайте формулу:
https://goo.gl/bGGIRR

Так вот, про саму формулу:
Она возвращает название книги с N-ыми по счету продажами, где N берется из ячейки D3. То есть, вводя туда число "4",  вы задаете вопрос: какая книга занимает 4 место в рейтинге продаж?
Работает так: функция LARGE (НАИБОЛЬШИЙ) находит N-ое по величине значения (в примере 310 - 7-ая по счету величина). Функция MATCH (ПОИСКПОЗ) находит порядковый номер строки, в которой находится это значение (1-ая строка в диапазоне), а функция INDEX (ИНДЕКС) возвращает значение, стоящее в этой строке, но в диапазоне A3:A14 (там, где названия).

Всем хороших выходных!

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

Формулы МАКС(клиенты) или МИН(средний_чек) будут выглядеть гораздо нагляднее формул МАКС(B1:B15) или МИН(C1:C15), к тому же такая запись может помочь вашим коллегам быстрее разобраться в вашем документе, если это будет необходимо.

Задать имя диапазону очень просто, выделите его, щелкните правой кнопкой мыши и выберите "определить именованный диапазон", далее введите для диапазона имя (без пробелов и тире)

В гифке - пример, в т.ч. считаем итоговые продажи с помощью формулы СУММПРОИЗВ (SUMPRODUCT) http://recordit.co/Xy8MupitMc.gif

По ссылке - Google Документ с примером https://goo.gl/bHNaFV
Чтобы редактировать документ, скопируйте его себе (Файл-> создать копию)

Друзья, сегодняшний выпуск рубрики "Бизнесхак" в блоге МИФа  посвящен Google Таблицам, поэтому делимся ссылкой здесь. В статье: наглядное представление таблиц с помощью чередующихся строк, ВПР (VLOOKUP) и вставка изображений в ячейки с помощью функции IMAGE:  http://blog.mann-ivanov-ferber.ru/2017/01/16/biznesxak-na-kazhdyj-den-vypusk-14-poleznye-funkcii-dlya-raboty-v-google-tablicax/

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

Лучше я вам покажу интересный кейс с формулой #ВПР(#VLOOKUP). Применять ее будем не "классическим" методом (см. пред. пост), а с аргументом интервальный просмотр = 1.

С этой настройкой формула будет искать в диапазоне поиска точное или ближайшее меньшее значение.

Например, мы решили наградить наших сотрудников премией по итогам месяца:
— за продажи до 149 - небольшая конфета🍬
— от 150 до 249 - воздушный шарик🎈
— 250+ - мороженое🍦

И при помощи одной формулы #ВПР мы можем сопоставить их продажи и сетку премий, чтобы понять, кто что получит.

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

Гифка с примером - http://g.recordit.co/RiyvIvpkMt.gif

Пример, с которым можно поиграться - в Google Документе (как обычно, файл-> создать копию, для редактирования) https://goo.gl/PWplfH

Сегодня расскажем о географической диаграмме в Google Таблицах (карте).
Она позволяет наглядно сравнивать регионы по каким-либо параметрам.

Допустим, страны по населению или потреблению электроэнергии. Или города по посещаемости футбольных матчей местных команд.

Построить ее просто: нужно подготовить данные со списком стран/городов и количественными значениями (в одном столбце - страны или города на русском или английском, во втором - числа, характеризующие какой-то показатель). А затем - дело техники:
Вставка->Диаграмма->Типы диаграмм->Карта

Смотрите гиф с примером (потребление пива в Европе):
http://g.recordit.co/eN0sinQ42u.gif

Подробное описание и несколько примеров в документе:
https://goo.gl/xMMbDo

А по этой ссылке файл с данными - копируйте себе на диск, изучайте, пробуйте построить разные диаграммы:
https://goo.gl/DojXtc

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

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

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

Гифка с примером - http://recordit.co/axwNaM0t6K.gif

В Google Таблице по ссылке - можно рассмотреть пример поближе и познакомиться с формулой, с помощью которой формируется второй выпадающий список. Как
обычно: файл-> создать копию, для того, чтобы увидеть формулы. https://goo.gl/jfI1HH

Добавляем изображение с ссылкой в ячейку.

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

Для этого понадобятся две функции - IMAGE (она позволяет вставить изображение в ячейку) и HYPERLINK (ГИПЕРССЫЛКА).

Смотрим гиф, а ниже - комментарии по формуле:
https://goo.gl/Z0dX1n

Функция IMAGE загружает в ячейку изображение по ссылке. Для нее мы подготовим ссылки на обложки наших книг.
Подробнее о функции IMAGE вы сможете прочитать здесь: http://shagabutdinov.ru/business-hacks/image_sheets/
У нее два аргумента - ссылка на изображение (заданное текстом в кавычках или в виде ссылки на ячейку таблицы, в которой хранится ссылка на изображение) и тип отображения (mode) - от которого зависит, как будет выглядеть картинка в ячейке. В нашем примере mode=2 - мы растягиваем изображение до размеров ячейки.

Функция HYPERLINK возвращает ссылку на веб-страницу. У нее два аргумента - ссылка и текст ссылки. Второй аргумент - это то, что будет записано в ячейке.
например: =HYPERLINK("www.yandex.ru";"Яндекс"). В ячейке будет слово "Яндекс", а при щелчке на нее пользователь перейдет по ссылке www.yandex.ru.
Подробнее про функцию HYPERLINK: http://shagabutdinov.ru/business-hacks/hyperlink/

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

По ссылке файл с примером и бонусом для продвинутых пользователей - там можно посмотреть, как  формировать ссылки на магазин автоматически из названия книги:
https://goo.gl/yZG9ZK

Один из самых простых способов ускорить работу документа в Таблицах - удалить ненужные строки и столбцы.

По умолчанию создается 1000 строк на каждом листе, и далеко не все пользователи удаляют лишнее. Если листов в документе много - большое количество строк (и столбцов) на каждом из них будет пагубно влиять на производительность.
Можно удалять строки "вручную" (выделять их, щелкать правой кнопкой мыши на номер любой строки и нажимать в контекстно меню на "Удалить строки...", а можно установить бесплатное дополнение Crop Sheet, которое делает эту работу за вас, автоматически удаляя все лишние ячейки.

У этого дополения всего две опции: Crop to data (обрезать все пустые, "по данным") и Crop to selection (обрезать по выделению, если вы хотите оставить дополнительные ячейки для дальнейшего расширения таблицы).
Смотрим на GIF пример с Crop to data:
https://goo.gl/Mhou6e

Друзья, сегодня разберем на простом примере формулу ГПР (это ВПР, только горизонтальный).

Формула производит поиск значения в первой строке диапазона и возвращает соответствующее значение из выбранной строки.

Аргументы этой формулы:
=ГПР(HLOOKUP)[1;2;3;4]
1) значение для поиска;
2) диапазон, в первой строке (верхней) которого будет идти поиск;
3) номер строки диапазона для возврата значения (горизонтально, сверху-вниз);
4) интервальный просмотр;

http://g.recordit.co/UAPNrrV3JH.gif

Ребята, привет.

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

Существует два варианта транспонирования:

1) с помощью формулы TRANSPOSE (ТРАНСП) — http://recordit.co/BqHtSoyJF9.gif

2) и с помощью копирования и специальной вставки — http://recordit.co/COCFw2RSNl.gif

Добрый день!
Сегодня поговорим о спарклайнах (графиках в ячейках). Допустим, у вас есть данные по продажам помесячно, и вы хотите посмотреть динамику по каждому ряду, но при этом не создавать большого количества отдельных диаграмм (это долго и неудобно, они будут занимать много пространства на листе и т.д.). Для этого и нужны спарклайны — мини-графики в ячейках, которые появились в версии Excel 2010.

К счастью, в Таблицах они тоже есть и, в отличие от Excel, реализованы в виде функции (в Excel их нужно вставлять через меню “Вставка”).
Функция так и называется: SPARKLINE.

На гиф посмотрим, как сделать график и гистограмму:
https://goo.gl/IrDT6m

Копируйте таблицу с данными и пробуйте создать спарклайны самостоятельно:
https://goo.gl/fJD0Mk

В документе по ссылке подробная информация по спарклайнам:
https://goo.gl/uZhzYf

Друзья, недавно мы разбирали формулу ТРАНСП (TRANSPOSE). И сегодня я решил показать вам интересный кейс с этой формулой, будем сортировать даные, расположенные горизонтально (то есть вдоль строк). http://recordit.co/5K2NzwlUDD.gif

Новичкам может быть сложновато, поэтому на формулу вблизи можно посмотреть в файле примере. Как обычно, файл-> создать копию, чтобы вы могли с ним работать. https://goo.gl/fSeZuR

Друзья, привет.

Как и в Excel, в Таблицах можно разбивать текст по столбцам. Эта опция находится в разделе меню “Данные” и называется “Разделить на колонки”.

Выделяем ячейки с текстом -> Заходим в раздел "Данные" на ленте -> "Разделить на колонки" -> Выбираем разделитель (например, запятую или пробел).

В примере разобьем столбец ФИО на три отдельных столбца с фамилиями, именами и отчествами. В данном случае разделителем является пробел.
Смотрим GIF:
https://goo.gl/ifXZHY

Всем хороших выходных!

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

http://recordit.co/Vj7e3JB6zC.gif

В гифке мы использовали только одно условие, но их можно быть сразу несколько. Примеры - в файле по ссылке (https://goo.gl/5iNTZV), как обычно, файл-> создать копию, чтобы с ним можно было работать.