Google Таблицы

2021-10-25 10:26:10 Удаляем строки в определенном порядке в Excel

Так, а что делать в Excel, где хоть и появились уже и ФИЛЬТР/FILTER с УНИК/UNIQUE (только в Office 365), но великая QUERY все еще отсутствует?
Вот лайфхак от автора книги "Эффективная работа в Microsoft Excel" Алана Мюррея.

1 В соседнем с данными пустом столбце вводим какой-нибудь символ (например, смайлик) для тех строк, которые нужно оставить, и другой - для тех, что нужно удалить (например, @).

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

3 Идем в "Найти и заменить" (FInd and Replace) - Ctrl + F или Ctrl + H - в поле "Найти" (Find what) вставляем символ из удаляемых строк и нажимаем "Найти все" (Find All).

4 Нажимаем Ctrl + A, чтобы выделить все найденные ячейки, закрываем окно, нажимаем Ctrl + - (вызов диалогового окна "Удалить") и выбираем "строку" (Entire Row). Открыть/Комментировать

2021-10-22 10:30:19 Надо бы диапазон проредить.

Друзья, ниже прекрасная статья от Михаила Смирнова. Слово автору:

Дальше вместо ... вставляем диапазон (из таблицы или результат каких-то формул).

Как убрать N первых строк диапазона?

=QUERY(...; "OFFSET N";)

Как убрать N первых столбцов диапазона?

=TRANSPOSE(QUERY(TRANSPOSE(...); "OFFSET N";))

Как убрать чётные строки диапазона?

=QUERY(...; "SKIPPING 2";)

Как убрать нечётные строки диапазона?

=QUERY(QUERY(...; "OFFSET 1";); "SKIPPING 2";)

Как убрать чётные столбцы диапазона?

=TRANSPOSE(QUERY(TRANSPOSE(...); "SKIPPING 2";))

Как убрать нечётные столбцы диапазона?

=TRANSPOSE(QUERY(QUERY(TRANSPOSE(...); "OFFSET 1";); "SKIPPING 2";))

Ну, вы поняли: для столбцов всё то же, что и для строк, только в начале и в конце делаем TRANSPOSE.

Ещё всё это можно совмещать.

Убираем чётные столбцы и строки:

=TRANSPOSE(QUERY(TRANSPOSE(QUERY(...; "SKIPPING 2";)); "SKIPPING 2";))

Оставляем каждую N-ю строку, начиная с первой:

=QUERY(...; "SKIPPING N";)

Оставляем каждую N-ю строку, начиная с M-ой (вместо M-1 надо вставить конкретное число):

=QUERY(QUERY(...; "OFFSET M-1";); "SKIPPING N";)

Полная версия статьи с примерами: telegra.ph/Kak-proredit-kolonki-i-stolbcy-diapazonov-v-Google-Sheets-10-20 Открыть/Комментировать

2021-10-21 10:01:00 ​​Хотите научиться работать в Power BI, строить отчёты и визуализировать данные для отслеживания показателей бизнеса? Испытайте себя в роли BI-аналитика и откройте для себя новую профессию на бесплатном онлайн-интенсиве от образовательной платформы Skillbox: https://clc.to/AbNe8A.

За 3 дня изучите основы BI-аналитики. Научитесь работать в Power BI, Excel и Google Sheets. Узнаете, как выбирать данные для анализа, строить отчёты и отслеживать разные показатели.

Занятия пройдут с 25 по 27 октября в 19:00 по московскому времени. Проведёт их специалист по разработке систем сквозной аналитики, автор блога prometriki.ru, участник образовательных программ по аналитике в Skillbox и GeekBrains — Иван Иванов. Он расскажет, кто такой BI-аналитик, чем он занимается и какие инструменты использует в работе.

Чтобы интенсив прошёл максимально эффективно, не забудьте перед началом установить Microsoft Excel и Power BI

Участвуйте, прокачивайте знания и получайте подарки! Всем, кто дойдёт до конца интенсива, подарим электронную книгу Пола Доэрти и Джеймса Уилсона «Человек + машина». А тем, кто выполнит практическую работу, — сертификаты на 10 000 рублей на любой курс образовательной платформы Skillbox. Один из победителей получит призы от «Logitech».

#реклама Открыть/Комментировать

2021-10-20 10:01:01 Обновляем Таблицу по ключу скриптом onEdit()

Привет, друзья! Сегодня рубрика – простой скрипт с комментариями по каждой строке кода.

Задача такая: есть таблица, имена и номера договоров, нужно автоматически менять все вхождения номера по выбранному имени.

Вот здесь (скрипт и комментарии), показываем, как задача решается с помощью триггера onEdit(). Как вы помните – onEdit() не надо запускать, он реагирует на все изменения Таблицы.

Таблица с решением (скопировать >)

=
Для заминки: в ячейке "H2" на листе "ЛистЛист" наш чекбокс, отследим с помощью onEdit(), что ячейка изменилась (чекбокс нажали или отжали) и выведем окно со словом "привет":

function onEdit(e) {
//определяем текущий лист, который редактируется и диапазон
let sheet = e.source.getActiveSheet();
let range = e.range;

//проверяем, редактируется ли ячейка "H2" на "ЛистЛист"
if (range.getA1Notation() == 'H2' && sheet.getName() == 'ЛистЛист!') {
//если да — что-то запускаем
Browser.msgBox('привет!')
}
} Открыть/Комментировать

2021-10-18 15:10:00 ​​Друзья, хочу пригласить вас на курс «Магия Excel», который уже во второй раз запускаем вместе с издательством МИФ.

Изучим 50+ функций и инструментов Excel, заглянем и в Google Таблицы.

Самое главное отличие второго потока — появился модуль для продвинутых, «Магистр». Самые отважные могут учиться на двух модулях сразу и получить комбо: 12 уроков и домашек вместо 6, а вместе с ними — незабываемые впечатления от этого курса. И, конечно, двойной объем знаний. Но даже если пойдете только на один - рассчитывайте свои силы, курс очень интенсивный, для проработки материала и выполнения домашек потребуется время - но только так можно закрепить знания.

На курсе будет много общения вживую на онлайн-уроках и в чате, домашки с проверкой (скриншоты обратной связи и ответов в чате из первого потока вы можете найти на странице курса, если есть любые вопросы - пишите напрямую @r_shagabutdinov), нескучная подача материала.

Стартуем 25 октября, курс длится 6 недель + будет выпускной, где разберем вопросы, типичные ошибки и лучшие решения. Для отличников будут подарки от МИФа (книги и курсы).

Для наших подписчиков — скидка 40% (на комбо, оба модуля сразу - даже больше) по промокоду Magic9.
Действует до 23.59 25 октября. Присоединяйтесь.

Подробнее о курсе можно почитать здесь→

С уважением, Ренат (и мои ассистенты — полноправные члены команды МИФ.Курсы — Штрудель и Лемур). Открыть/Комментировать

2021-10-18 11:05:17 Пользовательские форматы: мини-памятка Еще одна памятка - на этот раз по символам, используемым в кодах пользовательских (Custom) форматов. Напомним, что свои форматы создаются по адресу: Формат → Числа → Другие форматы → Другие форматы чисел Format → Number… Открыть/Комментировать

2021-10-15 10:45:09 Пользовательские форматы: мини-памятка

Еще одна памятка - на этот раз по символам, используемым в кодах пользовательских (Custom) форматов.
Напомним, что свои форматы создаются по адресу:

Формат → Числа → Другие форматы → Другие форматы чисел
Format → Number → More Formats → Custom number format

0 — незначащие нули (отображаются всегда). Если в формате указан один ноль, числа любой разрядности будут отображаться (то есть никакое число не будет «обрезаться»). Но если в формате указано несколько нулей, а числа в ячейках меньшей разрядности — нули все равно будут отображаться.

0% — процентный формат.

. (точка) — десятичная запятая.

, (запятая) — запятая после числа отображает числа в тысячах, миллионах и так далее. Одна - тысячи, две - миллионы и так далее. Например, 0,, — число будет отображаться в миллионах.

# — значащие цифры (отображаются, если на этой позиции есть значение). Например, 0.0# — формат, в котором всегда будет отображаться один знак после запятой (даже если число целое), а еще один — только если есть сотые.

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

* (звездочка) — звездочка с указанным после нее символом будет заполнять ячейку этим самым символом. Например, 0*- — число и затем повторяющиеся дефисы до конца ячейки.

\* — если нужно показывать в формате именно символ звездочки, то добавляем обратную косую черту.

_ (нижнее подчеркивание) — отступ на ширину символа, который будет указан после нижнего подчеркивания. Например, если у вас отрицательные числа в скобках, а положительные без, и вы хотите выровнять их, чтобы сами числа были ровно друг под другом, то в положительных можно сделать визуальный отступ на ширину отсутствующей скобки 0_);(0). Она не будет отображаться, вместо нее будет пустота шириной ровно со скобку.

[ColorN] — цвет. Помимо номеров (от 1 до 56) можно указывать цвет и текстом. Например, [Red].

@ — текст в ячейке. Например, @@@ — повторение текста трижды.

"текст" — текст в кавычках будет отображаться в формате. Например, если нужны числа с пробелом и сокращением "руб.": 0" руб."

; (точка с запятой) — Разделяет разные форматы. Есть 2 структуры: можно перечислить форматы (все или некоторые) для положительных, отрицательных чисел, нуля и текста. Указываются именно в таком порядке и через точку с запятой. Либо форматы для одного или двух условий (и всех остальных случаев). Условия задаются в квадратных скобках [Условие1] Формат ; [Условие 2] Формат; формат для остальных случаев.

Таблица с примерами пользовательских форматов (Создать копию) Открыть/Комментировать

2021-10-13 12:10:11 Регулярные выражения — компактная памятка от @vitalich

В Таблицах можно использовать регулярные выражения
1) для проверки строки на соответствие выражению (функция REGEXMATCH)
2) для замены части строки на другой текст (REGEXREPLACE)
3) для извлечения строки, которая соответствует регулярному выражению (REGEXEXTRACT)

Для Таблиц в регулярках достаточно знать (или самые часто используемые части RE2):
Классы символов: . \d \D \s \S \w \W \b \B (для \w нужно понимать, что не во всех средах будут матчится не-латинские буквы)
Пробельные спецсимволы: \n \r \t
Границы ^ $
Выбор или-или |
Выбор из набора [ ] и выбор всего, кроме символов из набора [^ ]
Квантификаторы жадные ( * + {n} {n,m} {n,} ) и как управлять их "жадностью" (*? +? {n,m}? {n,}?)
Извлекаемые группы ( )
Неизвелекаемые группы (?: )

Для отладки табличных выражений на regex101 слева надо включать Golang

Таблица с большим количеством примеров

PS Делитесь в комментариях тем, что помогает вам.

#regexp Открыть/Комментировать

2021-10-12 11:45:00 ​​ Приглашаем на практический интенсив по Power BI - "Сравнение периодов в DAX" от онлайн школы IQBI

13 октября, 19:00-21:30
Участие как всегда - бесплатное

Интенсив будет полезен как новичкам, так и тем, кто уже умеет работать с Power BI.

Чему вы научитесь:

1. Подключаться к файлам Excel и загружать данные в Power BI Desktop
2. Создавать таблицу дат
3. Создавать расчетные меры для показа данных за предыдущие периоды и показа накопительных итогов
4. Создавать интерактивный отчёт
5. Использовать Tabular Editor и группы вычислений для вывода возможностей ваших отчетов на новый уровень

Автор интенсива - Дмитрий Соловьев, сертифицированный тренер и MVP Microsoft.

Эксперт в создании систем бизнес-аналитики на платформе Power BI.

Более 10 лет работает с продуктами корпорации Microsoft в качестве инженера, разработчика и тренера. Специализируется на внедрении систем на базе Office 365, Microsoft Azure, Power BI.
Разрабатывает и внедряет системы самостоятельной бизнес-аналитики для финансовых и других департаментов зарубежных и российских компаний.

Регистрация по ссылке ниже
https://bit.ly/3lmWksY

#реклама Открыть/Комментировать

2021-10-11 11:45:09 Developer MetaData

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

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

Есть отличное решение, про которое почти никто не знает, хоть оно и описано в документации – Developer MetaData.

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

Примеры кода:
//создаём DM для листа "Лист с таким названием"
function createDM() {
SpreadsheetApp.getActive().getSheetByName('Лист с таким названием')
.addDeveloperMetadata("ключ для листа", "какое-то значение", SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT)
}

//обращаемся к DM по ключу и возвращаем актуальное название листа:
function getDM() {
Logger.log(SpreadsheetApp.getActive()
.createDeveloperMetadataFinder().withKey('ключ для листа').find()[0].getLocation().getSheet().getName());
}

Код в пастебин (пример с гифки тоже там)

PS Обратите внимание:
— DM не проверяет ключ на уникальность и вы можете создать несколько ключей с одинаковыми названиями (в коде же мы обращаемся к первому);
— Есть лимиты - это 30к символов на файл + 30к на каждый лист. Т.е. файл с 3 листами может суммарно сохранить 120к символов. В квоте считается каждый символ и ключа и значения; Открыть/Комментировать