Выделяем из диапазона только уникальные значения и подсчитываем их количество: функции 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

Функция IMPORTFEED позволяет загрузить на рабочий лист Google Таблиц данные из RSS-фида.
Ее первый и единственный главный аргумент - ссылка на фид (например, http://blog.mann-ivanov-ferber.ru/feed/). Как и в в аналогичных функциях (например, в IMPORTRANGE), ссылка либо берется в кавычки, либо вносится в ячейку, на которую вы потом ссылаетесь.

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

Подробнее о функции IMPORTFEED и ее аргументах читайте по ссылке:
https://goo.gl/wMJG7Y

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

В док-те с примером - таблица с изменяемыми статусами для строк (столбец А). В зависимости от выбранного статуса на строку действует правило условного форматирования и окрашивает ее в какой-то цвет. Чтобы рассмотреть таблицу поближе, файл> создать копию.

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

гифка - http://recordit.co/2JF4m7vD9H.gif
док-т - https://goo.gl/XmVfsJ

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

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

Для чего это нужно?
- Можно в файле поставить гиперссылку на исходные данные, которые находятся в другом месте листа, на другом листе документа или в другом документе;
- Можно быстро отправить коллеге ссылку в скайп или по почте - и не объяснять, что "данные на таком-то листе в правом верхнем углу начиная со столбца AM".

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

Смотрим GIF:
https://goo.gl/QgVG3U

Всем продуктивной пятницы и отличных выходных!

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

Сегодня рассмотрим кейс со спарклайнами. Спарклайн - формула, которая формирует график на основе ваших данных и помещает его в ячейку, куда введена.

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

Подробнее - в файле примере, файл-> создать копию, чтобы рассмотреть формулы поближе. Массив данных расположен на вкладке "данные".

https://goo.gl/VvcX2C

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

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

Надеемся, видео поможет с ней разобраться и начать пользоваться, если вы еще этого не делаете. Всем хорошей рабочей недели!
Ссылка на урок:
https://www.youtube.com/watch?v=HOTpjAqdalc

Привет, друзья! В этом посте я начну рассказывать о чудесной формуле QUERY (одна из немногих формул, которая есть только в Google таблицах, в MS Excel ее нет).

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

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

Гифка: http://recordit.co/fin3vivp7A.gif
Гугл-док с примером: (файл-создать копию, чтобы можно было с ним работать): https://goo.gl/wuvJcu

Привет, коллеги,

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

В предыдущем посте я писал про select и where в запросе QUERY, а сейчас для нашей задачи к ним добавляется group by, то есть группировка.

📌Пример: http://recordit.co/LEKrN5jCX3.gif

P.S. группировку можно сделать и по нескольким столбцам, просто перечислив их через запятую (group by A, B).

📌Горизонтальная версия таблички, элементы в столбцах (pivot вместо group by) http://recordit.co/eeI0jJYYFt.gif

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

На Нетологии открылась запись на мой курс "Google Таблицы: основы работы с инструментом для анализа данных".
Если хотите освоить все функции Google Таблиц и как следует попрактиковаться - добро пожаловать!

Подробное описание программы и ответы на вопросы - на сайте Нетологии:
http://netology.ru/programs/google-tables#!

Всем хороших праздничных дней!

С уважением, Ренат (один из авторов этого канала)

QUERY на основе нескольких диапазонов данных

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

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

В общем случае для нескольких источников данных синтаксис следующий:
=QUERY({диапазон 1;диапазон 2;...};"SELECT ...";[заголовки])

Допустим, у нас есть таблица со списком книг на одном листе - и со списком учебников на другом.
Объединим их в функции QUERY:
=QUERY({'Книги'!A1:D;'Учебники'!A2:D}...

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

В качестве условия укажем WHERE Col2 <> '', то есть все строки, в которых столбец 2 непустой (а значит, все строки с данными из двух таблиц)

Отсортируем по убыванию продаж 2016 года, которые у нас в четвертом столбец:
ORDER BY Col4 DESC

И в итоге получим следующую функцию:
=QUERY({'Книги'!A1:D;'Учебники'!A2:D};"SELECT * WHERE Col2 <> '' ORDER BY Col4 DESC";0)

Обратите внимание, что при использовании нескольких диапазонов данных в QUERY столбцы указываются в формате "ColN", где N - номер столбца.

Ссылка на файл с примером:
https://goo.gl/55UvGk

Всех поздравляем с праздниками!

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

Сегодня на повестке дня файл с двумя кейсами.

📌В прошлом посте мы коснулись объединения диапазонов для формулы QUERY. Сегодня продолжим про объединение, на вкладке "поиск в нескольких списках" в ячейке В2 формула, которая ищет значение из ячейки А2 сразу в двух столбцах, чтобы этого добиться нужно просто объединить в формуле диапазоны при помощи фигурных скобок и ";". Как вы понимаете, не обязательно ограничиваться двумя диапазонами, их может быть столько, сколько нужно. Если значение есть в списках, формула возвращает "ОК", если значения нет, то ошибку.


📌📌Второй кейс мы уже обсуждали несколько месяцев назад, но он мне кажется занимательным и я его повторю. На вкладке "выпадающий список.." показана реализация выпадающего списка (назовем его список №2), позиции которого зависят от того, что выбирает пользователь в выпадающем списке №1. Как это можно использовать? Ну, например вы отмечаете, кто оплатил платежку, в одном списке выбираете отдел, а в другом появляются авторизованные на оплату сотрудники этого отдела.


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