Друзья, сегодня я расскажу про недавно добавленную в таблички функцию SORTN, это привычная сортировка с возможностью задать количество возвращаемых элементов (n).
Синтаксис: SORTN(диапазон; [n]; [режим_показа_совпадений] (по умолч. = 0); [столбец_для_сорт1]; [по_возрастанию]; ....)
Например. вы можете с помощью IMPORTHTML загрузить данные из интернета, отсортировать и оставить первые несколько элементов, не загружая всю таблицу целиком.
Сортировать не обязательно, кстати, формула —-=SORTN(диапазон; кол-во элементов) просто выведет нужное кол-во строк из диапазона.
ГИФ: http://recordit.co/7LAHUMbVSD
ДОК-Т: https://goo.gl/1XXmJ2
📫для реквеста тем и пожеланий: https://goo.gl/LHCz14
Синтаксис: SORTN(диапазон; [n]; [режим_показа_совпадений] (по умолч. = 0); [столбец_для_сорт1]; [по_возрастанию]; ....)
Например. вы можете с помощью IMPORTHTML загрузить данные из интернета, отсортировать и оставить первые несколько элементов, не загружая всю таблицу целиком.
Сортировать не обязательно, кстати, формула —-=SORTN(диапазон; кол-во элементов) просто выведет нужное кол-во строк из диапазона.
ГИФ: http://recordit.co/7LAHUMbVSD
ДОК-Т: https://goo.gl/1XXmJ2
📫для реквеста тем и пожеланий: https://goo.gl/LHCz14
Google Docs
https://tttttt.me/google_sheets / Кейсы
REGEXEXTRACT: извлекаем фрагмент текста из ячейки по шаблону
Друзья, это первая наша публикация про функции, использующие регулярные выражения. Рег. выражения - специальные шаблоны или маски для поиска текста.
Итак, пример. Неприятная ситуация: в таблице, из которой вам нужно забрать числовые данные, они занесены как текст с дополнительными символами: "195 EUR", "итого - 220", "оплачено 800 долл" и так далее.
Извлечем числа из текстовых ячеек с помощью REGEXEXCTRACT - функции, извлекающей текст по маске - регулярному выражению.
О всем синтаксисе регулярных выражений вкратце не рассказать, к сожалению, но мы продолжим публикации по этой теме с другими примерами. С помощью регулярных выражений можно составить практически любой текстовый шаблон.
А мы скажем о функции.
Ее синтаксис:
=REGEXEXTRACT(текст;регулярное_выражение)
Текст может быть ссылкой на ячейку или быть собственно текстом в кавычках. Регулярное выражение записывается в кавычках.
В нашем случае текст - это ячейка с данными, а регулярное выражение будет выглядеть так:
[0-9]+
Что означает: любое количество (+) цифр от 0 до 9 ([0-9]), идущих подряд.
Обратите внимание, что функция извлечет числа по такому регулярному выражению. Но они все равно продолжат быть текстом для Google Таблиц.
Их нужно конвертировать в числа с помощью функции ЗНАЧЕН(VALUE).
Смотрим на GIF:
https://goo.gl/9G9HsB
Примеры других регулярных выражений:
Машин[а-я]{1} - все слова, состоящие из "Машин" и одной любой буквы. То есть Машина, Машины, Машине и т.д.
Долл|Руб - только текст "Долл" или "Руб".
Яндекс[а-я]{0,1} - Слово "Яндекс", а также слово "Яндекс" с любой буквой в конце, например, Яндекса, Яндексу и т.д.
Продолжение следует.
Друзья, это первая наша публикация про функции, использующие регулярные выражения. Рег. выражения - специальные шаблоны или маски для поиска текста.
Итак, пример. Неприятная ситуация: в таблице, из которой вам нужно забрать числовые данные, они занесены как текст с дополнительными символами: "195 EUR", "итого - 220", "оплачено 800 долл" и так далее.
Извлечем числа из текстовых ячеек с помощью REGEXEXCTRACT - функции, извлекающей текст по маске - регулярному выражению.
О всем синтаксисе регулярных выражений вкратце не рассказать, к сожалению, но мы продолжим публикации по этой теме с другими примерами. С помощью регулярных выражений можно составить практически любой текстовый шаблон.
А мы скажем о функции.
Ее синтаксис:
=REGEXEXTRACT(текст;регулярное_выражение)
Текст может быть ссылкой на ячейку или быть собственно текстом в кавычках. Регулярное выражение записывается в кавычках.
В нашем случае текст - это ячейка с данными, а регулярное выражение будет выглядеть так:
[0-9]+
Что означает: любое количество (+) цифр от 0 до 9 ([0-9]), идущих подряд.
Обратите внимание, что функция извлечет числа по такому регулярному выражению. Но они все равно продолжат быть текстом для Google Таблиц.
Их нужно конвертировать в числа с помощью функции ЗНАЧЕН(VALUE).
Смотрим на GIF:
https://goo.gl/9G9HsB
Примеры других регулярных выражений:
Машин[а-я]{1} - все слова, состоящие из "Машин" и одной любой буквы. То есть Машина, Машины, Машине и т.д.
Долл|Руб - только текст "Долл" или "Руб".
Яндекс[а-я]{0,1} - Слово "Яндекс", а также слово "Яндекс" с любой буквой в конце, например, Яндекса, Яндексу и т.д.
Продолжение следует.
REGEXMATCH: проверяем наличие в ячейке текста, отвечающего заданному шаблону
Друзья, привет.
Продолжаем знакомиться с функциями Google Таблиц, поддерживающими регулярные выражения.
Сегодня обсуждаем REGEXMATCH, проверяющую, содержится ли в ячейке текст, соответствующий регулярному выражению.
Возвращает эта функция логическое значение - ИСТИНА или ЛОЖЬ - в зависимости от того, найден ли в ячейке подходящий текст.
Обратите внимание, что проверяется наличие текста, отвечающего шаблону, а не полное соответствие всего текста, что есть в ячейке.
То есть для всех следующих значений текста в ячейке функция REGEXMATCH(ячейка; "Москва") вернет ИСТИНА:
Москва
Москва, ул. Ленина, 1
125125 Москва, 3-я улица Строителей, 25
В примере проверим:
1. Есть ли в ячейках номера банковских карт (вида 1234 5678 9012 3456). Регулярное выражение - "\d{4} \d{4} \d{4} \d{4}", где \d = цифра, {4} - кол-во идущих подряд цифр.
2. Есть ли в ячейках несколько строчных кириллических букв ("[а-я]+") или латинских ("[a-z]+").
Смотрим на GIF:
https://goo.gl/GL6GSX
Друзья, привет.
Продолжаем знакомиться с функциями Google Таблиц, поддерживающими регулярные выражения.
Сегодня обсуждаем REGEXMATCH, проверяющую, содержится ли в ячейке текст, соответствующий регулярному выражению.
Возвращает эта функция логическое значение - ИСТИНА или ЛОЖЬ - в зависимости от того, найден ли в ячейке подходящий текст.
Обратите внимание, что проверяется наличие текста, отвечающего шаблону, а не полное соответствие всего текста, что есть в ячейке.
То есть для всех следующих значений текста в ячейке функция REGEXMATCH(ячейка; "Москва") вернет ИСТИНА:
Москва
Москва, ул. Ленина, 1
125125 Москва, 3-я улица Строителей, 25
В примере проверим:
1. Есть ли в ячейках номера банковских карт (вида 1234 5678 9012 3456). Регулярное выражение - "\d{4} \d{4} \d{4} \d{4}", где \d = цифра, {4} - кол-во идущих подряд цифр.
2. Есть ли в ячейках несколько строчных кириллических букв ("[а-я]+") или латинских ("[a-z]+").
Смотрим на GIF:
https://goo.gl/GL6GSX
Друзья, привет.
Сегодня рассмотрим, как можно сделать ежедневную рассылку из Google Таблицы. Пример достаточно простой (мы убрали из него все регулярные выражения), поэтому, разобравшись, вы легко сможете адаптировать его под свои нужды. Все здесь: http://telegra.ph/Rassylator-09-15
📚Оглавление канала: https://goo.gl/HdS2qn
Сегодня рассмотрим, как можно сделать ежедневную рассылку из Google Таблицы. Пример достаточно простой (мы убрали из него все регулярные выражения), поэтому, разобравшись, вы легко сможете адаптировать его под свои нужды. Все здесь: http://telegra.ph/Rassylator-09-15
📚Оглавление канала: https://goo.gl/HdS2qn
Telegraph
Рассылатор
Таблица с примером: https://goo.gl/czzD57, (файл-создать копию, чтобы док-т сохранился на ваш диск и вы смогли в нем работать) В таблице два листа:
REGEXREPLACE. Находим и заменяем текст в ячейке, соответствующий регулярному выражению
Друзья, сегодня рассмотрим третью функцию Google Таблиц для работы с регулярными выражениями.
Это REGEXREPLACE, позволяющая заменить отдельные фрагменты, соответствующие регулярному выражению, на заданный текст.
Синтаксис:
=REGEXREPLACE (текст, в котором ведем поиск; рег.выражение - что заменяем в тексте ; текст, на который заменяем)
Например, функция:
=REGEXREPLACE(A1;"машин[а-я]{1}";"автомобиль")
Заменит в тексте из ячейки A1 все слова "машина", "машину" и другие ("машин" + любая буква) на "автомобиль".
Смотрим на GIF:
https://goo.gl/k8MtK2
Хорошего дня!
Друзья, сегодня рассмотрим третью функцию Google Таблиц для работы с регулярными выражениями.
Это REGEXREPLACE, позволяющая заменить отдельные фрагменты, соответствующие регулярному выражению, на заданный текст.
Синтаксис:
=REGEXREPLACE (текст, в котором ведем поиск; рег.выражение - что заменяем в тексте ; текст, на который заменяем)
Например, функция:
=REGEXREPLACE(A1;"машин[а-я]{1}";"автомобиль")
Заменит в тексте из ячейки A1 все слова "машина", "машину" и другие ("машин" + любая буква) на "автомобиль".
Смотрим на GIF:
https://goo.gl/k8MtK2
Хорошего дня!
#Простыевопросы: —Подскажите, как реализовать вывод 0 если функция filter ничего не нашла по выбранному диапазону? —Изи! Воспользуйтесь функцией Iferror (Еслиошибка).
Друзья, доброе утро.
Наверняка вы знаете, что с помощью IMPORTHTML можно заимпортировать таблицу из веб-страницы.
Например, формула в ячейке А1 скриншота (https://goo.gl/V4rr86) выведет таблицу из coinmarketcap.com, курс и параметры 100 криптовалют:
Наверняка вы знаете, что с помощью IMPORTHTML можно заимпортировать таблицу из веб-страницы.
Например, формула в ячейке А1 скриншота (https://goo.gl/V4rr86) выведет таблицу из coinmarketcap.com, курс и параметры 100 криптовалют:
🚬 а как вывести не всю таблицу, а только нужные нам поля?
Добавим функцию QUERY и выведем только 5 верхних строк:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT * LIMIT 5")
Только 1-ий, 3-ый столбец (обращаясь к ним Col+ номер столбца в источнике данных) и 10 строк:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT Col1, Col3 LIMIT 10")
Чтобы пропустить часть строк, используйте OFFSET, выведем строки с 11-ой по 15-ую:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT * LIMIT 5 OFFSET 10")
Разумеется, это будет работать не только с выбранной веб-страницей, а вообще с любой, на которой есть таблица или список :)
Хорошего дня!
📚Оглавление нашего канала: https://goo.gl/HdS2qn
Добавим функцию QUERY и выведем только 5 верхних строк:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT * LIMIT 5")
Только 1-ий, 3-ый столбец (обращаясь к ним Col+ номер столбца в источнике данных) и 10 строк:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT Col1, Col3 LIMIT 10")
Чтобы пропустить часть строк, используйте OFFSET, выведем строки с 11-ой по 15-ую:
=QUERY(IMPORTHTML("https://coinmarketcap.com/";"table";1);"SELECT * LIMIT 5 OFFSET 10")
Разумеется, это будет работать не только с выбранной веб-страницей, а вообще с любой, на которой есть таблица или список :)
Хорошего дня!
📚Оглавление нашего канала: https://goo.gl/HdS2qn
Google Docs
Оглавление / канал Google Таблицы: t.me/google_sheets
Диаграмма "Дерево" для отображения организационной структуры или других иерархий.
В Google Таблицах есть простая диаграмма "Дерево", с помощью которой можно визуализировать иерархическую структуру.
Данные для нее должны выглядеть несколько необычным образом:
- в первом столбце - дочерние элементы;
- во втором столбце - элементы верхнего уровня;
- в третьем - необязательно - примечания (они будут всплывать при наведении курсора на элемент).
В примере мы создали три строчки с CEO (верхний уровень), у которого три функциональных директора - подчиненных (они указаны в первом столбце).
И по две строчки на двух директоров, где они указаны уже во втором столбце, а их дочерние элементы (подчиненные) - в первом.
Как видите, CEO (самый верхний элемент) в первом столбце вообще не встречается.
А сотрудники, у которых нет подчиненных, наоборот, не встречаются во втором столбце.
Вот такая немного замысловатая структура данных для этой диаграммы.
ГИФ (открывайте по ссылке, пожалуйста): https://goo.gl/Xz6Vr2
В Google Таблицах есть простая диаграмма "Дерево", с помощью которой можно визуализировать иерархическую структуру.
Данные для нее должны выглядеть несколько необычным образом:
- в первом столбце - дочерние элементы;
- во втором столбце - элементы верхнего уровня;
- в третьем - необязательно - примечания (они будут всплывать при наведении курсора на элемент).
В примере мы создали три строчки с CEO (верхний уровень), у которого три функциональных директора - подчиненных (они указаны в первом столбце).
И по две строчки на двух директоров, где они указаны уже во втором столбце, а их дочерние элементы (подчиненные) - в первом.
Как видите, CEO (самый верхний элемент) в первом столбце вообще не встречается.
А сотрудники, у которых нет подчиненных, наоборот, не встречаются во втором столбце.
Вот такая немного замысловатая структура данных для этой диаграммы.
ГИФ (открывайте по ссылке, пожалуйста): https://goo.gl/Xz6Vr2
Друзья, как думаете, сколько нужно формул, чтобы отобрать из таблицы данные по условию, объединить построчно, добавить текст и превратив всё, например, в 30-ть ссылок апи? Или в поздравительные письма для имениников этого месяца?
Достаточно формулы, которую вы введёте только в одну ячейку. Нам поможет функция FILTER и объденинение диапазонов (и диапазонов с текстом либо с содержимым какой-то ячейки) с помощью амперсанда (&).
Пример в ГИФ: http://recordit.co/dJhWKjSiL7 / Документ: https://goo.gl/Ux7QsP
Достаточно формулы, которую вы введёте только в одну ячейку. Нам поможет функция FILTER и объденинение диапазонов (и диапазонов с текстом либо с содержимым какой-то ячейки) с помощью амперсанда (&).
Пример в ГИФ: http://recordit.co/dJhWKjSiL7 / Документ: https://goo.gl/Ux7QsP
Google Docs
Поздравительная открытка
Данные
Фамилия,Имя,Дата рождения
Текст 1,Текст 2,Андреев,Тимур,05.10.1987
Уважаемый,Примите мои сердечные поздравления, по случаю вашего дня рождения. Зная вас, как ответственного организатора и грамотного специалиста...,Аседов,Расул,01.06.1987
Месяц для…
Фамилия,Имя,Дата рождения
Текст 1,Текст 2,Андреев,Тимур,05.10.1987
Уважаемый,Примите мои сердечные поздравления, по случаю вашего дня рождения. Зная вас, как ответственного организатора и грамотного специалиста...,Аседов,Расул,01.06.1987
Месяц для…
Друзья, добрый вечер. Представьте: вы работаете с табличкой, в которую попадают чеки из нескольких пиццерий (структура таблички: точка продаж; дата-время чека; номер чека; сумма чека).
Попробуем ответить на несколько вопросов по этим данным и так уж получилось, что формулы в этот раз вышли довольно угрожающими :)
Подобные, большие и страшные формулы, проще читать изнутри, начиная от вложенных элементов, в примере 1, мы начинаем с того, что используя QUERY отбираем по каждой площадке дату-время последнего чека, далее полученный массив используем в FILTER, как диапазон элементов условия, оставляя в таблице чеки (строки) только за это время.
1) Как вывести последний чек по каждой пиццерии?
=FILTER(A1:D11;NOT(ISERROR(MATCH(B1:B11;QUERY(A2:B11;"select max(B) group by A";0);0))))
2) Как вывести первый чек за сегодня по каждой пиццерии?
=FILTER(A1:D11;NOT(ISERROR(MATCH(B1:B11;QUERY(A1:B11;"select min(B) where B>=date '2017-10-16' group by A";0);0))))
3) И выведем сумму чеков за вчерашний день по каждой точке продаж:
=QUERY(A1:D11;"select A, sum(D) where B>=date '2017-10-15' and B<date '2017-10-16' group by A")
Желаю всем хорошего вечера!
ГУГЛТАБЛИЧКА: https://goo.gl/n7PaJE
📚Оглавление нашего канала: https://goo.gl/HdS2qn
Попробуем ответить на несколько вопросов по этим данным и так уж получилось, что формулы в этот раз вышли довольно угрожающими :)
Подобные, большие и страшные формулы, проще читать изнутри, начиная от вложенных элементов, в примере 1, мы начинаем с того, что используя QUERY отбираем по каждой площадке дату-время последнего чека, далее полученный массив используем в FILTER, как диапазон элементов условия, оставляя в таблице чеки (строки) только за это время.
1) Как вывести последний чек по каждой пиццерии?
=FILTER(A1:D11;NOT(ISERROR(MATCH(B1:B11;QUERY(A2:B11;"select max(B) group by A";0);0))))
2) Как вывести первый чек за сегодня по каждой пиццерии?
=FILTER(A1:D11;NOT(ISERROR(MATCH(B1:B11;QUERY(A1:B11;"select min(B) where B>=date '2017-10-16' group by A";0);0))))
3) И выведем сумму чеков за вчерашний день по каждой точке продаж:
=QUERY(A1:D11;"select A, sum(D) where B>=date '2017-10-15' and B<date '2017-10-16' group by A")
Желаю всем хорошего вечера!
ГУГЛТАБЛИЧКА: https://goo.gl/n7PaJE
📚Оглавление нашего канала: https://goo.gl/HdS2qn
Google Docs
Пиццерии
Друзья, мы рады поделиться с вами новостью о выходе нашей книги в издательстве МИФ!
Это первое в России издание, посвященное редактору электронных таблиц от Google.
Книга доступна в электронном формате на сайте МИФа. И - что замечательно - ее выход совпал с запуском "электронной корзины" на сайте издательства, в честь чего скидка на все электронные книги составляет 50% до завтрашнего дня! То есть книгу можно приобрести всего лишь за 199 рублей.
Ну а после окончания акции - для тех наших подписчиков, кто не успел приобрести книгу по акции - мы предоставляем промокод на скидку 20%, которая будет суммироваться с другими скидками. Промокод действует до 30 октября: ndrkjl3
Книга на сайте издательства:
https://www.mann-ivanov-ferber.ru/books/google-tabliczyi-eto-prosto/
P.S. Материал в книге дополнен по сравнению с первой версией. Так что обновилась не только обложка :) добавился материал в главах про сводные, фильтры и спарклайны. Те, кто приобретал предыдущую версию, могут написать нам и получить новую версию бесплатно.
Это первое в России издание, посвященное редактору электронных таблиц от Google.
Книга доступна в электронном формате на сайте МИФа. И - что замечательно - ее выход совпал с запуском "электронной корзины" на сайте издательства, в честь чего скидка на все электронные книги составляет 50% до завтрашнего дня! То есть книгу можно приобрести всего лишь за 199 рублей.
Ну а после окончания акции - для тех наших подписчиков, кто не успел приобрести книгу по акции - мы предоставляем промокод на скидку 20%, которая будет суммироваться с другими скидками. Промокод действует до 30 октября: ndrkjl3
Книга на сайте издательства:
https://www.mann-ivanov-ferber.ru/books/google-tabliczyi-eto-prosto/
P.S. Материал в книге дополнен по сравнению с первой версией. Так что обновилась не только обложка :) добавился материал в главах про сводные, фильтры и спарклайны. Те, кто приобретал предыдущую версию, могут написать нам и получить новую версию бесплатно.
Издательство «МИФ»
Google Таблицы. Это просто (Евгений Намоконов, Ренат Шагабутдинов) — купить в МИФе
Практика и ничего кроме практики. Электронная книга (epub, pdf, mobi, fb2). Читать отзывы и скачать главу.
Друзья, всем привет.
Наш сегодняшний кейс о том, как вывести финансовый месяц по нужной нам дате (например, 19-10-2017) из таблицы вида:
дата начала периода | фин. месяц | фин. год
...
15-09-2017 | 08 | 2017
18-10-2017 | 09 | 2017
Нам поможет интервальный просмотр = 1, он есть в функциях ВПР и ПОИСКПОЗ (MATCH). Он ищет ближайшее меньшее значение в диапазоне поиска к искомому. Таблица должна быть отсортирована по столбцу поиска, по возрастанию (можно и формулой).
1) =MATCH(E1;A1:A10;1) - возвращает 2, это номер нужной нам строки
2) =INDIRECT("B"& MATCH(E1;A1:A10;1) ) - добавляем название столбца, который мы хотим вывести и превращаем текст в настоящую ссылку на ячейку с помощью INDIRECT(ДВССЫЛ)
P.S. показатели, равные 1, вводить не обязательно, MATCH(E1;A1:A10;1) = MATCH(E1;A1:A10)
https://goo.gl/d7FE1g
Наш сегодняшний кейс о том, как вывести финансовый месяц по нужной нам дате (например, 19-10-2017) из таблицы вида:
дата начала периода | фин. месяц | фин. год
...
15-09-2017 | 08 | 2017
18-10-2017 | 09 | 2017
Нам поможет интервальный просмотр = 1, он есть в функциях ВПР и ПОИСКПОЗ (MATCH). Он ищет ближайшее меньшее значение в диапазоне поиска к искомому. Таблица должна быть отсортирована по столбцу поиска, по возрастанию (можно и формулой).
1) =MATCH(E1;A1:A10;1) - возвращает 2, это номер нужной нам строки
2) =INDIRECT("B"& MATCH(E1;A1:A10;1) ) - добавляем название столбца, который мы хотим вывести и превращаем текст в настоящую ссылку на ячейку с помощью INDIRECT(ДВССЫЛ)
P.S. показатели, равные 1, вводить не обязательно, MATCH(E1;A1:A10;1) = MATCH(E1;A1:A10)
https://goo.gl/d7FE1g