​​Друзья, сегодня у нас два вопроса - один нам пришлось уточнить, а в другом случае получилось решить несложную, но любопытную задачу по аккуратному сцеплению текста в одной ячейке с переходами на новые строки . В качестве орудия пользовались замечательной функцией СМЕЩ, формирующей ссылку на динамический диапазон. Наш вестник.

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

Тема, навеянная нашим чатом. Как запретить вводить формулы в ячейки?

Отвечаем:
Выделяете ячейки > "проверка данных" > "ваша формула" > =not(isformula(верхняя-левая ячейка выбранного диапазона) и "запрещать ввод данных". Все, теперь формулу не ввести. Чтобы разрешить ввод только формул - убираем not.

Хозяйке на заметку: используя эти же формулы вы можете создать правило условного форматирование, которое выделит все формулы (или наоборот).

Друзья, сегодня делимся с вами еще одним роликом из онлайн-курса по Таблицам в SkillFactory.

"Условное форматирование и логические функции. Как выделять строки по одному или нескольким условиям"
https://www.youtube.com/watch?v=1O6SWDDsVZk

Коллеги, доброе утро. В сегодняшнем видео добавляем к изображению товара ссылку на него. И изображение и ссылку вставляем функциями (IMAGE + HYPERLINK).

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

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

=REGEXEXTRACT(A2;"[А-Я][а-я]+")
Расшифровка: извлекаем имя, т.е. первую прописную букву - [А-Я] и любое кол-во строчных после - [а-я]+

=SUBSTITUTE(A2;B2;"") / =ПОДСТАВИТЬ(A2;B2;"")
И с помощью функции "=подставить" меняем в нашей строке имя, полученное выше на "" (пустоту). Остается фамилия.

​​Друзья, в сегодняшнем посте:

1. сделаем автоматическую сквозную нумерацию работ в смете

2. сделаем условное форматирование, чтобы строки с заголовками разделов заливались серым цветом автоматически

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

Желаем всем хороших выходных!

https://shagabutdinov.ru/answers_02062018/

Друзья, доброе утро.

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

Отбирать будем разными способами: FILTER / QUERY / функция массива + IF.

​​Доброе утро.

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

У этой задачи много спобосов решения: MINIFS или ПОИСКПОЗ + СМЕЩ (или ИНДЕКС) или ВПР, FILTER, QUERY...

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

1) Если столбцы "дата" и "кассир" стоят рядом (J5:K7)
=FILTER(J5:K7; J5:J7 = MAX(J5:J7)

2) Столбцы не рядом, создаем массив и объединяем два нужных диапазона {\}
=FILTER({J11:J13 \ L11:L13};J11:J13=MAX(J11:J13))

P.S.
- чтобы вывести минимальную дату - меняем в условии MAX на MIN
- для второй наименьшей или третьей наибольшей даты - меняем условие на
=НАИМЕНЬШИЙ(J5:J7;2); =НАИБОЛЬШИЙ(J5:J7;3)

​​Друзья, привет. Сегодня у нас выпадающий список с "одноразовыми" значениями. После использования они будут исчезать из списка.

Итак,
1) в A4:A - все исходные варианты;
2) в B4:B - загружаем формулой то, что уже было выбрано;
3) в C4:4 - магия, оставляем разницу списков A4:A и B4:B, этот же диапазон используется для создания выпадающего списка.

Формулы,
2) B4: =FILTER(F4:F;F4:F<>"")
3) C4: =FILTER(A4:A7;ISNA(MATCH(A4:A7;B4:B;0)))

Таблица с примером.

​​Друзья, сводная таблица - лучше кучи формул.

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

​​Но абсолютно такого же результата вам позволит добиться одна сводная таблица. Или в простых случаях - одна функция QUERY.

Таблица с примером

​​Друзья, публикуем памятку по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах).

Теперь вы всегда будете знать, как отобрать по дате в QUERY, что писать в кавычках, а что нет и где нужен амперсанд (&).

Таблица с примером

P.S. А в этом посте мы писали о том, как отобрать по пустым ячейкам, по непустым, по ячейкам с ""

Субботние ответы на ваши вопросы

Друзья, всем привет, ответы—по ссылке.

—Добавляем новую строку скриптом и вставляем в нее текущую дату
—ДВССЫЛ в QUERY и выпадающий список с выбором месяца

​​Рассчитываемое поле в сводных таблицах

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

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

Для начала создадим сводную таблицу, в строки добавим "Менеджер".
В значения - рассчитываемое поле (оно прячется в "добавить").
Рядом с параметром "Суммировать по" выберите Другое.

Теперь:
1. Посчитаем среднюю стоимость метра по менеджеру, формулой будет:
=sum('Сумма')/sum('Кв. м.') (где 'Сумма' и 'Кв. м.' – столбцы исходных данных).

2. Посчитаем сумму оплаченных квартир у каждого менеджера:
=SUMIFS('данные'!C2:C7;'данные'!D2:D7;"да";'данные'!A2:A7;'Менеджер')

Названия полей из исходных данных указываются в апострофах. Таблица с примером

​​Google Форма + Google Таблица для проведения тестирования.

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

​​Выделяем месяц из даты

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

1. Функция =TEXT(a1;"mmmm"), где a1—дата, вернет: Июня, Июля, Августа и т.д. Функция изменит только форматирование (отображение) вашей даты, вы увидите месяц и он будет в родительном падеже. Сама же дата останется прежней и эту ячейку вы сможете использовать в формулах.

2. Для извлечение месяца в любом падеже нам нужны будут INDEX и MONTH, дата при этом превратится в текстовую строку.

Внутри диапазона INDEX задаем массив с "правильными названиями":
=index({"ЯНВАРЬ"\"ФЕВРАЛЬ"\"МАРТ"\"АПРЕЛЬ"\"МАЙ"\"ИЮНЬ"\"ИЮЛЬ"\"АВГУСТ"\"СЕНТЯБРЬ"\"ОКТЯБРЬ"\"НОЯБРЬ"\"ДЕКАБРЬ"})

Вторым аргументом будет функция MONTH(дата), она возвращает порядковый номер месяца (1-12) и с помощью нее же мы вернем из массива нужный нам элемент, формула целиком:
=index({"ЯНВАРЬ"\"ФЕВРАЛЬ"\"МАРТ"\"АПРЕЛЬ"\"МАЙ"\"ИЮНЬ"\"ИЮЛЬ"\"АВГУСТ"\"СЕНТЯБРЬ"\"ОКТЯБРЬ"\"НОЯБРЬ"\"ДЕКАБРЬ"};month(A2))

​​Как проставить номера всех столбцов на листе?

Хочется видеть номера столбцов в таблице или же они нужны, чтобы легче прописать запрос в QUERY из другого файла (ведь, как мы неоднократно обсуждали, при запросе к другому файлу или к нескольким диапазонам в QUERY нужно писать Col1, Col4, а не A, C)
Как быть?

Простая формула массива:
=ARRAYFORMULA(COLUMN(1:1))
=ARRAYFORMULA(СТОЛБЕЦ(1:1))

Вводите ее в A1 и во всех ячейках первой строки будут номера столбцов.
При этом можно удалять и добавлять столбцы - всегда автоматически будут номера во всех из них.
Конечно, такую же операцию можно и со строками провести (функция ROW/СТРОКА)

P.S. И два субботних ответа на ваши вопросы.