Видеоурок по Excel для новичков: разделение и объединение текста

Друзья, привет! Делимся с вами уроком из курса «Магия Excel» — про то, как разделять текст на столбцы и как, наоборот, объединять несколько ячеек в одну строку.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/#rec493432600

В курсе 55 таких видеоуроков — про сводные таблицы, функции поиска (ВПР, ИНДЕКС) и формулы массива, Power Query, визуализацию данных и даже обзор свежих функций 2022 года. Всего обучающего материала — на 700+ минут.

Курс «Магия Excel» прошли больше 1000 учеников, средняя оценка — 4,9 из 5.

Коллеги из МИФа сделали промокод на скидку 40% — по промокоду Magia40. Действует до полуночи 10 октября.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/

Выделяем на диаграмме текущий месяц

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

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

Этот столбец будет вторым рядом данных - который будет "поверх" основного, и это будет выглядеть как выделение отдельных точек/периода. Этот ряд можно сделать с большим контуром и более ярким цветом.

Формула в общем виде
=ЕСЛИ(условие, по которому выбираются точки для выделения;значение из столбца с данными;"")

Например, если мы выделяем текущий месяц на диаграмме:
=ЕСЛИ(МЕСЯЦ(СЕГОДНЯ())=месяц в этой строке;значение в этой строке;"")

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

JOIN / TEXTJOIN по каждой строке в новых реалиях

Друзья, функции JOIN и TEXTJOIN соединяют значения из нескольких ячеек в одной.

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

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

=BYROW(A1:D5;LAMBDA(row;TEXTJOIN(" ";1;row)))

Как это работает
— В BYROW передаем диапазон, далее функция передает каждую строку диапазона в LAMBDA;
— В LAMBDA каждая строка диапазона используется как аргумент для функции TEXTJOIN;
— TEXTJOIN, в свою очередь, объединяет значения с разделителем пробел, отбрасывая пустые ячейки и выводит результат в каждую строку;

А замените BYROW на BYCOL - сможете соединять значения по столбцам 🤩

Еще про новые функции:
Накопительный итог построчно
Подсчёт значений построчно

Находим последнее значение с помощью XLOOKUP

Вы ищете значение в таблице, и вам нужно получить данные из последней, а не первой строки с искомым значением. Например, с последней продажей или курсом (последним в данном случае = по расположению строк, по сортировке).

ВПР / VLOOKUP ищет "сверху вниз", то есть если искомое значение встречается несколько раз, будет возвращаться первое (верхнее) значение.

Но теперь у нас есть новая функция XLOOKUP!
А в ней - аргумент "search_mode" (режим поиска). Задаем его равным -1 (минус единице), чтобы искать "снизу вверх".
По умолчанию он равен 1 (единице, это стандартный вариант "сверху вниз").

Функция в общем виде будет выглядеть так:
=XLOOKUP(искомое значение; просматриваемый диапазон ; возвращаемый диапазон ;;;-1)

Аргумент с режимом поиска последний, обратите внимание, что мы пропускаем два других: [missing_value] и [match_mode]. Это аргументы для возвращения значения в случае ошибки (когда ничего не найдено) и для использования символов подстановки/примерного поиска. В данном случае мы оставляем их по умолчанию - то есть в случае отсутствия искомого значения будет ошибка N/A, и будет вестись точный поиск без использования символов подстановки.

Ссылка на таблицу с примером

P.S. Конечно, с ВПР тоже можно пошаманить, добавив другую функцию - об этом мы писали аж 4 года назад - вот тут.

Немного Excel-экзотики: проговаривание ячеек (текст в речь)

Есть в Excel и такая опция. Можно воспроизвести содержимое ячеек - это касается и текста на русском/английском, и дат, и чисел.
Как и многие команды, эта недоступна на ленте инструментов. Ее можно добавить на панель быстрого доступа (Quick Access Toolbar).

Заходим в параметры Excel - Панель быстрого доступа (либо на самой панели в выпадающем списке выбираем "Настроить панель быстрого доступа").
Выбираем в выпадающем списке "Выбрать команды из" - "Все команды" (Choose commands from - All Commands). Вот она, магия - тут действительно все команды Excel, ряд из которых нигде больше не найдешь в принципе (например, то же проговаривание ячеек или мастер сводных таблиц и диаграмм из старых версий приложения).

Список длинный - вводите первую букву команды, чтобы быстрее найти необходимое.

Нас с вами интересуют команды "Проговорить ячейки" (Speak Cells) и "Прекратить проговаривание ячеек" (Stop Speaking).
Последняя выключит проговаривание принудительно. Иначе же будут проговорены все выделенные ячейки.
Если выделена одна ячейка, будут проговариваться и смежные.

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Сколько в Таблице
листов?
ячеек?
ячеек заполнено?
можно еще создать ячеек?

Привет, друзья, такой вопрос задали недавно в нашем чате.

Покажем простой скрипт, который ответит на все эти вопросы. Чтобы получилось "образовательно" – расскажем про каждую строчку этого скрипта.

Код и комментарии: pastebin.com/e2vva9Rr

💡 Заходите в наш чатик и задавайте вопросы, но только сначала вам нужно будет пройти капчу, а для этого придется зайти в оглавление канала :)

Пара фокусов с "найти и заменить" из нашего чата

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

Добавим в конце каждой строки <br/>, убирая перенос строки

1) Найти и заменить
2) Найти: \n|$
3) Заменить на: <br/>
4) Галочку на "использование регулярных выражений"
5) Заменить всё!

Другие примеры "найти и заменить"

Наш чат

Ссылка на другую Таблицу может быть с http или без, с указанием листа (/edit#gid=0) в конце или без, давайте заменим любые вхождения ссылки на на что-то другое.

1) Найти и заменить
2) Найти: (.+)ID Таблицы$|(.+)
3) Заменить на: наш текст
4) Галочку на "использование регулярных выражений"
5) Заменить всё!

Другие примеры "найти и заменить"

Наш чат

Используем функцию QUERY из GAS-скриптов

Отсортировать, отфильтровать и сгруппировать данные с помощью функции QUERY можно из скриптов Google Таблиц.

Показываем пример от @vitalich.

Функция в Таблице:
=QUERY(A1:C8; "SELECT A, SUM(B) WHERE C>30 GROUP BY A";1)

И её реализация в скриптах:
function queryAPI() {
let sheetId = SpreadsheetApp.getActive().getId();
let sheet = 'Sheet1';
let req = 'SELECT A, Sum(B) WHERE C>30 GROUP BY A';
let outputFormat = 'out:csv';
let urlTemplate = 'https://docs.google.com/spreadsheets/d/%s/gviz/tq?tqx=%s&sheet=%s&tq=%s';
let url = Utilities.formatString(urlTemplate, sheetId, outputFormat, sheet, encodeURIComponent(req));
let params = {
method: 'get',
headers: {
'Authorization': "Bearer " + ScriptApp.getOAuthToken(),
muteHttpExceptions: true
}
};
let query = UrlFetchApp.fetch(url, params);
let text = query.getContentText();
let output = text.split('\n').map(f => f.split(',').map(g => JSON.parse(g)));
console.log(output);
let sheetPaste = SpreadsheetApp.getActive().getSheetByName('result');
sheetPaste.clearContents();
sheetPaste.getRange(1, 1, output.length, output[0].length).setValues(output);
};

Вводим 2,3,5 в ячейку и вторая, третья и пятая строки выделяются /УСЛОВНОЕ ФОРМАТИРОВАНИЕ

Друзья, показываем простой трюк.

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

Как это реализовать:
1) Строки, которые нужно выделить будем вводить в ячейку E2;
2) Выделяем диапазон данных, у нас это A:C;
3) Условное форматирование;
4) Добавить правило > форматирование формулой > вводим формулу:
=match(row($A1); split($E$2;",");0)

Что делает формула УФ: делит ячейку с номерами строк по разделителю запятая с помощью SPLIT, получает массив номеров, далее ищет каждый номер строки в этом массиве с помощью MATCH, если находит - возвращается ИСТИНА и условное форматирование закрашивает эту строку.

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

Чипируем таблицы и документы

В таблицы и документы можно вставлять чипы (smart chips) - нарядные ссылки на пользователей, документы (другие таблицы, например), события в календаре.
Можно через меню "Вставка". А можно просто ввести собачку @ и начать вводить название события из календаря, электропочту коллеги или название таблицы/документа.

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

Добавление чипа с пользователем не откроет ему доступ автоматически.

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

А в Google Документах можно еще и даты чипировать. Например, ввести завтрашнюю дату.

Помимо чипов, о которых мы писали чуть выше, в Google Документах есть раскрывающиеся списки. Хорошая штука, чтобы выбирать один из нескольких статусов.
Можно использовать один из стандартных шаблонов или создать свой.

Вставка - Раскрывающийся список

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

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

=IF(вы_любите_наш_канал ; поддержите_нас ; )

Друзья, мы с 2017 года помогаем вам с таблицами и скриптами.

Если хотите поддержать нас в ответ - будем очень признательны. Мы тут прикрутили кнопку для донатов к нашему каналу - будем рады, если вы опробуете эту штукенцию в деле!

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / DATEDIF

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст) — пользуйтесь функцией РАЗНДАТ / DATEDIF. Она не является документированной в Excel (то есть при ее вводе не будут отображаться всплывающая подсказка с аргументами, Excel не предложит ее дописать, ее нет в справке), но не обращайте на это внимание — она работает во всех версиях. И в Google Таблицах тоже!

 =РАЗНДАТ(дата_начала; дата_окончания; единица измерения)

Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.

Единица измерения задается в кавычках. Есть следующие возможные варианты:

"d" — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
"m" — число полных месяцев в периоде;
"y" — число полных лет в периоде;
"md" — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
"ym" — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
"yd" — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

Сумма по строке в новых реалиях, c условием!
(💥 пост с домашним заданием)

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

Сегодня будем считать сумму по столбцам D, E, F в формуле массива при условии, что столбец A непустой.

Итак, формула целиком:
=BYROW(A1:F10;LAMBDA(ROW;if(INDEX(ROW;1)<>"";SUM(OFFSET(ROW;0;3;1;3));)))

Что мы в ней делаем:
— В функцию BYROW передаем весь диапазон
— В функции LAMBDA обращаемся к каждой строке диапазона как к ROW
— В привычной функции IF / ЕСЛИ обращаемся к первому элементу строки с помощью INDEX, проверяя есть ли значение
— Если IF / ЕСЛИ возвращает истину, то с помощью функции OFFSET / СМЕЩ отступаем от первой ячейки строки три столбца и суммируем этот отрезок
— Если IF / ЕСЛИ возвращает ложь, то не возвращаем ничего

💥 Домашнее задание: попробуйте написать формулу массива для столбца B с использованием новых функций, формула должна суммировать столбцы D, E, F для непустого столбца A. Отправьте свой вариант в комментарии.

Про новые функции:
Накопительный итог построчно
Подсчёт значений построчно
JOIN построчно