Друзья, привет!
🗞Мы написали статью на хабр: https://habrahabr.ru/post/331360/
В ней подробно, с примерами, про некоторые функции Таблиц, которых нет в Excel: (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE).
Будете искать информацию по этим функциям - заходите.
🗞Мы написали статью на хабр: https://habrahabr.ru/post/331360/
В ней подробно, с примерами, про некоторые функции Таблиц, которых нет в Excel: (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE).
Будете искать информацию по этим функциям - заходите.
Habr
Полезные функции Google Таблиц, которых нет в Excel
Cтатья написана в соавторстве с Ренатом Шагабутдиновым. В этой статье речь пойдет о нескольких очень полезных функциях Google Таблиц, которых нет в Excel (SORT, объединение массивов, FILTER,...
📚 Друзья, оглавление канала, все темы и даты постов: https://goo.gl/HdS2qn
Google Docs
Оглавление / канал Google Таблицы: t.me/google_sheets
🎈🎈🎈Друзья, привет.
Сегодня утром мы преодолели пугающее число в 666 подписчиков. Кому-то может показаться, что это немного, но мы рады каждому читателю, который сможет взять у нас что-то полезное и сделать свою работу с таблицами лучше , чтобы сберечь время для близких людей и любимых дел
✉️Сегодня мы хотим задать вам пару вопросов и обратиться за помощью с выбором новых тем, актуальных для вас: https://goo.gl/LHCz14
Это займет всего 1-2 минуты. Будем благодарны за ваши ответы.
Если наш канал приносит вам пользу, расскажите о нас в соцсетях (например, запостив оглавление или интересный для вас пост) Нам будет очень приятно - а ваши друзья тоже смогут узнать новое о работе в Таблицах. И спасибо, что вы с нами!
Евгений и Ренат.
Сегодня утром мы преодолели пугающее число в 666 подписчиков. Кому-то может показаться, что это немного, но мы рады каждому читателю, который сможет взять у нас что-то полезное и сделать свою работу с таблицами лучше , чтобы сберечь время для близких людей и любимых дел
✉️Сегодня мы хотим задать вам пару вопросов и обратиться за помощью с выбором новых тем, актуальных для вас: https://goo.gl/LHCz14
Это займет всего 1-2 минуты. Будем благодарны за ваши ответы.
Если наш канал приносит вам пользу, расскажите о нас в соцсетях (например, запостив оглавление или интересный для вас пост) Нам будет очень приятно - а ваши друзья тоже смогут узнать новое о работе в Таблицах. И спасибо, что вы с нами!
Евгений и Ренат.
Google Docs
Google Таблицы - вопросная.
👍1
Всем привет!
Как правило, в Таблицах в одном документе одновременно работает несколько человек.
И часто сразу нескольким пользователям нужно фильтровать данные.
Можно ставить фильтры так, чтобы они не были видны остальным пользователям - и не мешали им работать.
Обычный фильтр виден всем пользователям, аналогичен таковому в Excel и вызывается нажатием соответствующей кнопки на ленте инструментов.
А вот Режим фильтрации виден только одному пользователю. Несколько пользователей могут создавать свои, разные и независимые фильтры благодаря этой опции. И не мешать друг другу работать с документом.
Режим фильтрации можно вызвать, нажав на маленькую кнопку со стрелкой рядом с иконкой фильтра и нажав на пункт “Создать новый фильтр” в выпадающем меню.
https://goo.gl/14FubJ
Созданные вами в этом режиме фильтры сохраняются - к ним можно вернуться в будущем, нажав на название в выпадающем списке. Поэтому - и особенно если пользователей много - стоит сразу задавать фильтру название, чтобы понимать, чей это фильтр и/или для чего он нужен.
P.S. Друзья, напоминаю , что уже скоро - 30 июня - стартует мой онлайн-курс в Нетологии по Google Таблицам:
http://netolo.gy/dzi
будем учиться всему - от азов и простых формул до визуализации и продвинутых функций.
По промокоду googlesheets - скидка 20% :)
Как правило, в Таблицах в одном документе одновременно работает несколько человек.
И часто сразу нескольким пользователям нужно фильтровать данные.
Можно ставить фильтры так, чтобы они не были видны остальным пользователям - и не мешали им работать.
Обычный фильтр виден всем пользователям, аналогичен таковому в Excel и вызывается нажатием соответствующей кнопки на ленте инструментов.
А вот Режим фильтрации виден только одному пользователю. Несколько пользователей могут создавать свои, разные и независимые фильтры благодаря этой опции. И не мешать друг другу работать с документом.
Режим фильтрации можно вызвать, нажав на маленькую кнопку со стрелкой рядом с иконкой фильтра и нажав на пункт “Создать новый фильтр” в выпадающем меню.
https://goo.gl/14FubJ
Созданные вами в этом режиме фильтры сохраняются - к ним можно вернуться в будущем, нажав на название в выпадающем списке. Поэтому - и особенно если пользователей много - стоит сразу задавать фильтру название, чтобы понимать, чей это фильтр и/или для чего он нужен.
P.S. Друзья, напоминаю , что уже скоро - 30 июня - стартует мой онлайн-курс в Нетологии по Google Таблицам:
http://netolo.gy/dzi
будем учиться всему - от азов и простых формул до визуализации и продвинутых функций.
По промокоду googlesheets - скидка 20% :)
👍1
Привет!
Сегодня решим следующую задачу по форматированию:
Допустим, у вас есть данные по продажам по дням/неделям/другим периодам. Периоды - в столбцах. Название товаров (магазинов/филиалов/проч.) - в строках.
Как одним условием выделить в каждой строке одним цветом 25% "лучших" дней (с наибольшими продажами)?
1. Выделить весь диапазон;
2. Формат -> Условное форматирование -> Ваша формула
3. Прописать формулу: =[первая ячейка диапазона]>QUARTILE([диапазон с закрепленными столбцами],3)
Например: =B2>QUARTILE($B2:$Z18;3). Ключевой момент здесь в закреплении столбцов. Так одной формулой вы сможете корректно использовать условное форматирование для каждой строки из диапазона. То есть в каждой строке каждое значение будет сравниваться с 75% квартилью (второй аргумент функции, равный 3 - это 75% квартиль) - и если оно больше, т.е. входит в четверть лучших, то оно будет покрашено.
Конечно, можно использовать и другие формулы в условном форматировании. Или наложить второе условие и вдобавок покрасить 25% худших с помощю формулы =B2<QUARTILE($B2:$Z18;1)
Смотрим GIF:
https://goo.gl/fs8AL6
Файл:
https://goo.gl/H2HigS
(данные в примере придуманы генератором случайных чисел)
Функция QUARTILE на русском - КВАРТИЛЬ.
Сегодня решим следующую задачу по форматированию:
Допустим, у вас есть данные по продажам по дням/неделям/другим периодам. Периоды - в столбцах. Название товаров (магазинов/филиалов/проч.) - в строках.
Как одним условием выделить в каждой строке одним цветом 25% "лучших" дней (с наибольшими продажами)?
1. Выделить весь диапазон;
2. Формат -> Условное форматирование -> Ваша формула
3. Прописать формулу: =[первая ячейка диапазона]>QUARTILE([диапазон с закрепленными столбцами],3)
Например: =B2>QUARTILE($B2:$Z18;3). Ключевой момент здесь в закреплении столбцов. Так одной формулой вы сможете корректно использовать условное форматирование для каждой строки из диапазона. То есть в каждой строке каждое значение будет сравниваться с 75% квартилью (второй аргумент функции, равный 3 - это 75% квартиль) - и если оно больше, т.е. входит в четверть лучших, то оно будет покрашено.
Конечно, можно использовать и другие формулы в условном форматировании. Или наложить второе условие и вдобавок покрасить 25% худших с помощю формулы =B2<QUARTILE($B2:$Z18;1)
Смотрим GIF:
https://goo.gl/fs8AL6
Файл:
https://goo.gl/H2HigS
(данные в примере придуманы генератором случайных чисел)
Функция QUARTILE на русском - КВАРТИЛЬ.
👍1
⁉️Подписчик спрашивает: как разделить колонку со счетом игры и посчитать отдельно сумму для каждой стороны?
🔉Отвечаем:
1) SPLIT умеет парсить ячейку, а если добавить функцию массива - то одной формулой можно сразу разделить весь столбец;
2) Результат SPLIT - два столбца, как просуммировать один из них на лету, не выводя в рабочую книгу? Добавим функцию INDEX, (если не писать номер строки, а только номер столбца из массива - формула оставит только его);
3) И завершаем все SUM, чтобы просуммировать столбец и получить одно число;
🔎Гифка с примером: http://recordit.co/5N3Co0LcQ3
📚Оглавление канала: https://goo.gl/HdS2qn
Всем хороших выходных и спасибо за интересные вопросы!
🔉Отвечаем:
1) SPLIT умеет парсить ячейку, а если добавить функцию массива - то одной формулой можно сразу разделить весь столбец;
2) Результат SPLIT - два столбца, как просуммировать один из них на лету, не выводя в рабочую книгу? Добавим функцию INDEX, (если не писать номер строки, а только номер столбца из массива - формула оставит только его);
3) И завершаем все SUM, чтобы просуммировать столбец и получить одно число;
🔎Гифка с примером: http://recordit.co/5N3Co0LcQ3
📚Оглавление канала: https://goo.gl/HdS2qn
Всем хороших выходных и спасибо за интересные вопросы!
Google Docs
Оглавление / канал Google Таблицы: t.me/google_sheets
⁉️Читательница спрашивает: возможно ли отфильтровать ячейки по наличию в них формулы?
🔉Да, например, с помощью классического фильтра:
1. выбираем наши данные-> 2. фильтр-> 3. фильтровать по условию-> 4. ваша формула
5. вводим формулу: =isformula(A2) (для отбора в колонке А диапазона A1:B6)
🔎Гифка с примером: http://recordit.co/hjfnJkrhp2
⁉️Задать свой вопрос: https://goo.gl/LHCz14
🔉Да, например, с помощью классического фильтра:
1. выбираем наши данные-> 2. фильтр-> 3. фильтровать по условию-> 4. ваша формула
5. вводим формулу: =isformula(A2) (для отбора в колонке А диапазона A1:B6)
🔎Гифка с примером: http://recordit.co/hjfnJkrhp2
⁉️Задать свой вопрос: https://goo.gl/LHCz14
Google Docs
Google Таблицы - вопросная.
Коллеги, привет!
В функции IMPORTRANGE можно использовать именованные диапазоны (не C2:ZE150, а "База_Данных", например).
Присвоить диапазону имя можно так: Меню "Данные" -> Именованные диапазоны.
Далее вместо обычного "Лист!A1:Z100" в функции IMPORTRANGE во втором аргументе можно указать имя диапазона, например: "Продажи", где "Продажи" - имя диапазона.
Обратите внимание, что открытому диапазону (например, A1:Z) нельзя присвоить имя. Только обычному, а-ля A1:Z1000.
Смотрим на гифке:
https://goo.gl/eDvRRn
Напоминаем, что наш видеоурок по функции IMPORTRANGE есть на Ютубе: https://www.youtube.com/watch?v=HOTpjAqdalc
В функции IMPORTRANGE можно использовать именованные диапазоны (не C2:ZE150, а "База_Данных", например).
Присвоить диапазону имя можно так: Меню "Данные" -> Именованные диапазоны.
Далее вместо обычного "Лист!A1:Z100" в функции IMPORTRANGE во втором аргументе можно указать имя диапазона, например: "Продажи", где "Продажи" - имя диапазона.
Обратите внимание, что открытому диапазону (например, A1:Z) нельзя присвоить имя. Только обычному, а-ля A1:Z1000.
Смотрим на гифке:
https://goo.gl/eDvRRn
Напоминаем, что наш видеоурок по функции IMPORTRANGE есть на Ютубе: https://www.youtube.com/watch?v=HOTpjAqdalc
Коллеги, привет.
Объединив функции ВПР и ПОИСКПОЗ в одну формулу массива, можно одной формулой подтягивать все нужные данные из исходной таблицы - по названиям столбцов.
Даже если вы поменяете порядок столбцов в целевой таблице, все данные подтянутся корректно.
Единственное условие - столбец с данными, по которым производится поиск, должен быть первым в исходной таблице (как обычно в ВПР).
Смотрим на GIF:
https://goo.gl/mvkNbU
(лист с исходными данными в примере называется "ВПР")
Формула:
=ARRAYFORMULA(ВПР(текст_для_поиска;исходная_таблица;ПОИСКПОЗ(заголовки_поиск;заголовки_исходная;0);0))
=ARRAYFORMULA(VLOOKUP(текст_для_поиска;исходная_таблица;MATCH(заголовки_поиск;заголовки_исходная;0);0))
выводим массив, состоящий из результата работы функции ВПР, в которой номера столбцов для вывода данных введены не вручную, а определяются с помощью функции ПОИСКПОЗ, которая находит заголовки из вашей таблицы в исходной таблице и возвращает их порядковые номера.
📚Оглавление канала: https://goo.gl/HdS2qn
Объединив функции ВПР и ПОИСКПОЗ в одну формулу массива, можно одной формулой подтягивать все нужные данные из исходной таблицы - по названиям столбцов.
Даже если вы поменяете порядок столбцов в целевой таблице, все данные подтянутся корректно.
Единственное условие - столбец с данными, по которым производится поиск, должен быть первым в исходной таблице (как обычно в ВПР).
Смотрим на GIF:
https://goo.gl/mvkNbU
(лист с исходными данными в примере называется "ВПР")
Формула:
=ARRAYFORMULA(ВПР(текст_для_поиска;исходная_таблица;ПОИСКПОЗ(заголовки_поиск;заголовки_исходная;0);0))
=ARRAYFORMULA(VLOOKUP(текст_для_поиска;исходная_таблица;MATCH(заголовки_поиск;заголовки_исходная;0);0))
выводим массив, состоящий из результата работы функции ВПР, в которой номера столбцов для вывода данных введены не вручную, а определяются с помощью функции ПОИСКПОЗ, которая находит заголовки из вашей таблицы в исходной таблице и возвращает их порядковые номера.
📚Оглавление канала: https://goo.gl/HdS2qn
👍3
Друзья, сегодня я расскажу вам про то, как превратить логические значения ИСТИНА/ЛОЖЬ в числа 1/0 и как это можно использовать.
Введем в любую ячейку формулу "=1=1", формула возвращает слово ИСТИНА. А теперь берем выражение в скобки и добавляем перед ними два минуса. Формула превращает ИСТИНУ в 1 и ЛОЖЬ в 0.
Как это можно использовать в работе? Ну, например посчитаем сумму по ставке 100, если коэффицент больше 1,6.
Берем функцию СУММПРОИЗВ (SUMPRODUCT), функция вычисляет сумму произведений в диапазонах, диапазоны условий мы превращаем в 1 и 0, то есть все лишнее умножится на 0 и отсеется.
Формула: =SUMPRODUCT(--(A2:A13=100);--(B2:B13>1,6);D2:D13)
https://image.prntscr.com/image/0dhkWbGSTcqg2fE0z8mbjQ.png
Всем хороших выходных, берегите себя!
Введем в любую ячейку формулу "=1=1", формула возвращает слово ИСТИНА. А теперь берем выражение в скобки и добавляем перед ними два минуса. Формула превращает ИСТИНУ в 1 и ЛОЖЬ в 0.
Как это можно использовать в работе? Ну, например посчитаем сумму по ставке 100, если коэффицент больше 1,6.
Берем функцию СУММПРОИЗВ (SUMPRODUCT), функция вычисляет сумму произведений в диапазонах, диапазоны условий мы превращаем в 1 и 0, то есть все лишнее умножится на 0 и отсеется.
Формула: =SUMPRODUCT(--(A2:A13=100);--(B2:B13>1,6);D2:D13)
https://image.prntscr.com/image/0dhkWbGSTcqg2fE0z8mbjQ.png
Всем хороших выходных, берегите себя!
👍1
⁉️И сразу апдейт: наш подписчик Р. пожаловался на то, что он не понимает, как работает формула с СУММПРОИЗВ в этом случае 😕
На скриншоте - объяснение, может пригодится кому-то еще (столбцы F, G, H - так выглядят диапазоны "внутри" нашей формулы)
🔎=SUMPRODUCT(--(A2:A13=100);--(B2:B13>1,6);D2:D13)
https://image.prntscr.com/image/VKSdmY3WQIqU5lIcZBOoUg.png
На скриншоте - объяснение, может пригодится кому-то еще (столбцы F, G, H - так выглядят диапазоны "внутри" нашей формулы)
🔎=SUMPRODUCT(--(A2:A13=100);--(B2:B13>1,6);D2:D13)
https://image.prntscr.com/image/VKSdmY3WQIqU5lIcZBOoUg.png
Друзья, салют!
Google Таблицы умеют объединять значения из ячеек (выбранных диапазонов) и выводить в одну ячейку. Это делает функция =JOIN(разделитель; диапазон(ы)). Есть и обратная функция, =SPLIT(диапазон(ы); разделитель), она делит текст по разделителю и выводит в отдельные ячейки.
📌Несколько примеров с объединением (смотрите скриншот):
(1️⃣) Если все поля заполнены, то достаточно только JOIN (ячейки Е2 и F2).
(2️⃣) Если в выбранных диапазонах есть пустые значения, то JOIN будет выводить лишние разделители (ячейка F3). Немного изменим формулу, добавим SPLIT и еще один JOIN (SPLIT уберет лишние разделители). Теперь все хорошо (ячейка F4).
(3️⃣) Зачем вообще объединять? Например, чтобы сделать условие для формулы QUERY (ячейка C6), формулу на скриншоте показывать не буду, все-таки суббота 😊. Хороших выходных!
https://image.prntscr.com/image/fSJgyd2GRwWdvSgcVcLS7w.png
📚Гугл-док: https://goo.gl/oTcCjc
Google Таблицы умеют объединять значения из ячеек (выбранных диапазонов) и выводить в одну ячейку. Это делает функция =JOIN(разделитель; диапазон(ы)). Есть и обратная функция, =SPLIT(диапазон(ы); разделитель), она делит текст по разделителю и выводит в отдельные ячейки.
📌Несколько примеров с объединением (смотрите скриншот):
(1️⃣) Если все поля заполнены, то достаточно только JOIN (ячейки Е2 и F2).
(2️⃣) Если в выбранных диапазонах есть пустые значения, то JOIN будет выводить лишние разделители (ячейка F3). Немного изменим формулу, добавим SPLIT и еще один JOIN (SPLIT уберет лишние разделители). Теперь все хорошо (ячейка F4).
(3️⃣) Зачем вообще объединять? Например, чтобы сделать условие для формулы QUERY (ячейка C6), формулу на скриншоте показывать не буду, все-таки суббота 😊. Хороших выходных!
https://image.prntscr.com/image/fSJgyd2GRwWdvSgcVcLS7w.png
📚Гугл-док: https://goo.gl/oTcCjc
👍1
Друзья, привет!
🔥Первое на нашем канале "готовое решение" - Google табличка, которая рисует графики по выбранной валютной паре за период и даже считает, подпадает колебание курса под закон Парето или нет.
🔎 В табличке на первом листе можно выбрать валютную пару (например, биткоин к рублю) и период. Графики обновятся.
📈А графики там такие:
(1️⃣) изменения курса
(2️⃣) ежедневного относительного изменения
(3️⃣) ежедневного абсолютного изменения (в %)
Табличка родилась, пока я отвечал на важнейший вопрос современности: "подчиняется ли изменение курса биткоина к рублю за весь период наблюдения правилу Парето, то есть 80% изменения за 20% торговых дней" (НЕТ 😕). Все расчеты и график - на листе Парето, все формулы сохранены, лист будет пересчитываться при выборе другой валютной пары и/или периода.
https://image.prntscr.com/image/b5rCq1zbQcqi0bgIJKApKw.png
📚Гугл-док: https://goo.gl/3SoJKb (файл-> создать копию, чтобы сохранить к себе и пользоваться)
📌Чтобы изменить формат отображения дат на графике (например, на дд-мм-гг - поменяйте формат столбца А на первом листе)
📌Большое спасибо Сергею Багузину и его прекрасному сайту по Excel за идею.
🔥Первое на нашем канале "готовое решение" - Google табличка, которая рисует графики по выбранной валютной паре за период и даже считает, подпадает колебание курса под закон Парето или нет.
🔎 В табличке на первом листе можно выбрать валютную пару (например, биткоин к рублю) и период. Графики обновятся.
📈А графики там такие:
(1️⃣) изменения курса
(2️⃣) ежедневного относительного изменения
(3️⃣) ежедневного абсолютного изменения (в %)
Табличка родилась, пока я отвечал на важнейший вопрос современности: "подчиняется ли изменение курса биткоина к рублю за весь период наблюдения правилу Парето, то есть 80% изменения за 20% торговых дней" (НЕТ 😕). Все расчеты и график - на листе Парето, все формулы сохранены, лист будет пересчитываться при выборе другой валютной пары и/или периода.
https://image.prntscr.com/image/b5rCq1zbQcqi0bgIJKApKw.png
📚Гугл-док: https://goo.gl/3SoJKb (файл-> создать копию, чтобы сохранить к себе и пользоваться)
📌Чтобы изменить формат отображения дат на графике (например, на дд-мм-гг - поменяйте формат столбца А на первом листе)
📌Большое спасибо Сергею Багузину и его прекрасному сайту по Excel за идею.
Друзья, салам! 🎈
Мы с удивлением обнаружили наш канал на 1-ом и 2-ом месте выдачи Яндекса, обогнали даже оф. сайт Google Табличек (нет, не платили) https://goo.gl/fUzPce
Здорово, что мы нашли свою аудиторию, будем и дальше стараться делиться интересными кейсами (на днях, кстати, разберем простой Dashboard 📊)
📌Тем, кто пришел недавно, хочу посоветовать нашу статью на хабре, она о крайне полезных функциях Таблиц , которых нет у Excel: https://habrahabr.ru/post/331360/
📚Оглавление канала: https://goo.gl/HdS2qn
Мы с удивлением обнаружили наш канал на 1-ом и 2-ом месте выдачи Яндекса, обогнали даже оф. сайт Google Табличек (нет, не платили) https://goo.gl/fUzPce
Здорово, что мы нашли свою аудиторию, будем и дальше стараться делиться интересными кейсами (на днях, кстати, разберем простой Dashboard 📊)
📌Тем, кто пришел недавно, хочу посоветовать нашу статью на хабре, она о крайне полезных функциях Таблиц , которых нет у Excel: https://habrahabr.ru/post/331360/
📚Оглавление канала: https://goo.gl/HdS2qn
👍1
Жеребьевка футбольного кубка: функции ИНДЕКС и СЛУЧМЕЖДУ
Друзья, привет. Сегодня посмотрим, как можно выбрать случайную пару команд из двух разных либ, чтобы провести жеребьевку кубка.
Задача состоит из двух простых этапов:
1. Выбрать случайные числа от 1 до N, где N - количество команд в лиге (функция СЛУЧМЕЖДУ);
2. Вернуть названия команд, соответствующие этим номерам, из списка (функция ИНДЕКС).
Формула для одного из клубов будет выглядеть следующим образом:
Можно сформировать названия клубов в отдельных ячейках, как в примере, можно сразу сформировать пару с помощью текстовых функций:
Пример - на скриншоте.
Друзья, привет. Сегодня посмотрим, как можно выбрать случайную пару команд из двух разных либ, чтобы провести жеребьевку кубка.
Задача состоит из двух простых этапов:
1. Выбрать случайные числа от 1 до N, где N - количество команд в лиге (функция СЛУЧМЕЖДУ);
2. Вернуть названия команд, соответствующие этим номерам, из списка (функция ИНДЕКС).
Формула для одного из клубов будет выглядеть следующим образом:
=ИНДЕКС(список_клубов; СЛУЧМЕЖДУ(1;N))Можно сформировать названия клубов в отдельных ячейках, как в примере, можно сразу сформировать пару с помощью текстовых функций:
=ИНДЕКС(список_клубов1; СЛУЧМЕЖДУ(1;N))&" : "&ИНДЕКС(список_клубов2; СЛУЧМЕЖДУ(1;M))Пример - на скриншоте.