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

Лучше я вам покажу интересный кейс с формулой #ВПР(#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), как обычно, файл-> создать копию, чтобы с ним можно было работать.

Друзья, сегодня говорим о функции NETWORKDAYS (ЧИСТРАБДНИ), возвращающей количество рабочих дней между двумя указанными датами с учетом праздников.
Синтаксис такой: NETWORKDAYS (От, До, [Праздники]), где первые два аргумента - даты, третий (необязательный) - диапазон с праздничными днями.

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

GIF:
https://goo.gl/KDlWKl

Подробнее о функции и кейсе:
https://goo.gl/YUw6nA

Файл с примером:
https://goo.gl/SgcbPl

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

Это легко поправить, ведь как и в MS Excel, в Google Таблицах есть возможность зафиксировать определенные строки/столбцы на экране.

http://recordit.co/yfnUJSH3O1.gif

Выделяем из диапазона только уникальные значения и подсчитываем их количество: функции UNIQUE и COUNTUNIQUE.

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

Обратите внимание, что мы задаем открытый диапазон вида A2:A, а не A2:A20, благодаря чему список уникальных значений обновляется, когда мы добавляем в исходный список новые пункты.

Это демонстрируется на GIF:
https://goo.gl/mxrxld

Файл с примером:
https://goo.gl/aYwQgr

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

Удобнее всего это провернуть с помощью формулы массива, разобранный пример в Google Документе (файл -> скачать копию, чтобы видеть формулы). Формула получилась достаточно непростой и я разобрал ее написание по этапам, в столбцах Е, F и G.

https://goo.gl/MzNrtY

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

У этой функции три аргумента: ссылка на сайт, запрос (table - таблица или list - список) и порядковый номер элемента (списка или таблицы) на сайте.

Например, =IMPORTHTML(" http://cbr.ru";"table";3) импортирует данные из третьей по порядку таблицы на главной странице Центрального банка.
Аргументы можно задавать как текст в кавычках (для первых двух аргументов) и число (для третьего), а можно и сослаться на ячейки, в которых они записаны. Так можно будет быстрее менять параметры и подгружать разные таблицы/списки.

Именно так мы и сделаем в примере (GIF):
https://goo.gl/WWJwYO

Файл с примером:
https://goo.gl/WYgbBX

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

Сегодня я покажу, как с помощью формулы ПОИСКПОЗ можно сравнить два списка.

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

Синтаксис: =ПОИСКПОЗ(искомое значение; диапазон для поиска; точный поиск, этот аргумент должен равняться нулю, если нам нужен точный поиск).

http://recordit.co/T0O4dRxAUk.gif

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

(если формула вернула число, то элемент из списка 1 есть в списке 2, если Н/Д - элемента нет)

Конвертируем текст в число.

Друзья, привет.
Вы наверняка знаете, что не нужно вводить в качестве разделителя разрядов пробел вручную:
100 000
Потому что тогда в Таблицах введенное значение будет восприниматься как текст, а не как число.
Вместо этого нужно отформатировать ячейку как числовую (Формат -> Числа -> Число)

Но как быть (а это реальный пример из моей практики), если вы загружаете данные из чужого документа или интернета, а там все числа введены с пробелами как текст?

Воспользоваться следующей формулой, чтобы превратить их в нормальные числа:
=VALUE(SUBSTITUTE(число_как текст;" ";""))
=ЗНАЧЕН(ПОДСТАВИТЬ(число_как текст ;" ";""))

Функция VALUE (ЗНАЧЕН) конвертирует текст в число. А SUBSTITUTE (ПОДСТАВИТЬ) удаляет все пробелы из текста, заменяя их пустой строкой (пустые кавычки = текстовая строка нулевой длины)

Смотрим на GIF:
https://goo.gl/qW8HNI

Привет, друзья.

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

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

И самый важный момент: если в формуле условного форматирования мы напишем =C1="г. Липецк", то правило выделит только ячейки первого столбца выбранного для форматирования диапазона, а если =$C1="г. Липецк" - правило выделит строки диапазона целиком.

И гифка с примером: http://recordit.co/yMddsfu2l6.gif

Друзья, привет!
Поздравляем девушек с прошедшим праздником. Всем хорошей погоды, красоты, здоровья и любви :)

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

Для этого возьмем первый символ из ячейки:
ЛЕВСИМВ(ячейка; 1)

Переведем его в верхний регистр:
ПРОПИСН(ЛЕВСИМВ(ячейка; 1))

А теперь возьмем весь остальной текст без первого символа:
ПРАВСИМВ(ячейка; ДЛСТР(ячейка) - 1)

И переведем его в нижний регистр:
СТРОЧН(ПРАВСИМВ(ячейка; ДЛСТР(ячейка) - 1))

А теперь объединим все в одну формулу (приводим на двух языках):
=UPPER(LEFT(A1; 1))&LOWER(RIGHT(A1; LEN(A1) - 1))
=ПРОПИСН(ЛЕВСИМВ(A1; 1))&СТРОЧН(ПРАВСИМВ(A1; ДЛСТР(A1) - 1))

Кстати, формула будет работать и в Excel.

Документ с примером:
https://goo.gl/RgugcK