Уникализатор / пользовательская функция от нашего канала

Привет! Работает так: передаёте в функцию диапазон и указываете номер столбца с ключом, а функция:

1) возврат вам только по одной строке с каждым ключом;
2) эти строки будут "максимально обогащенными" - функция оставит в каждой ячейке строки последнее непустое значение по ключу строки (смотрите что получилось на скриншоте, ключи - Пушкин и Гумилёв);
3) на входе отфильтровываются все #N/A и #REF;

Функция текстом:
function unique_googlesheets(arr, key) {
key = key - 1;
let o = {};

arr.map(row => row.map(cell => ['#REF!', '#N/A'].includes(cell) ? '' : cell))
.forEach(y => {
if (!(y[key] in o)) {
o[y[key]] = y;
} else {
o[y[key]] = o[y[key]].map((h, i) => y[i] || h)
}
})
return Object.values(o);
}

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

★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat
★ Избранные посты нашего канала: t.me/google_sheets/418

Оберни колонки: новая (относительно) функция WRAPCOLS

Итак, нам с вами нужно превратить одномерный массив — например, столбец, в котором данные цикличные (время начала мероприятия + N строк с выступающими в нашем примере) — в двумерный, разместив каждый повторяющийся "блок" в отдельный столбец.

Засунем диапазон в WRAPCOLS, вторым аргументом укажем, сколько ячеек отправлять в каждый столбец. Необязательный третий аргумент — как возвращать пустые ячейки из исходника, если они там будут. Иначе будет выводиться ошибка #N/A (#Н/Д).

=WRAPCOLS(A1:A;N; [чем заменить пустые])

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

=WRAPCOLS(FILTER(A1:A;A1:A<>"");N)

P.S. Раз есть функция WRAPCOLS — значит — это кому-нибудь нужно? есть и WRAPROWS.
P.P.S. В Excel (365) при русскоязычном интерфейсе — СВЕРНСТОЛБЦ и СВЕРНСТРОК.

Отбираем по ошибке #N/A строки в QUERY и FILTER

Друзья, иногда в наших датасетах может быть ошибка #N/A (not available или значение не доступно).

Показываем, как отобрать строки или значения с ошибкой и без:

1. Для QUERY, отбираем строки с N/A:
=QUERY(A1:A12;"where Col1 = '#N/A'";0)

2. Для QUERY, отбираем строки без N/A:
=QUERY(A1:A12;"where Col1 != '#N/A'";0)

3. Чтобы отобрать N/A в FILTER:
=FILTER(A1:A12;ISNA(A1:A12))

4. Ну и отбираем всё, кроме N/A в FILTER:
=FILTER(A1:A12;NOT(ISNA(A1:A12)))

Функция ТИП.ОШИБКИ / ERROR.TYPE

Даем ей ячейку, получаем порядковый номер ошибки (число). Смотрим в справку:
1 для ошибки #NULL!
2 для ошибки #DIV/0!
3 для ошибки #VALUE!
4 для ошибки #REF!
5 для ошибки #NAME?
6 для ошибки #NUM!
7 для ошибки #N/A
8 для всех других ошибок.

То есть можно отлавливать определенные ошибки по схеме:

=ЕСЛИ(ТИП.ОШИБКИ(ячейка)=2; "Кажется, тут случилось деление на ноль"; вычисление)

Еще напомним про другие функции:
ЕОШ / ISERR — возвращает ИСТИНА / TRUE для всех ошибок, кроме Н/Д (N/A)
ЕНД / ISNA — ИСТИНА только для Н/Д
IFNA — сразу заменяет N/A на второй аргумент, а без ошибки возвращает первый аргумент.

Смотрите также:
Как выделить ячейки с ошибками REF внутри формул
Подробная статья про ошибки в формулах


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

Возвращаемся к нашему посту с курсами.

1) Мы вывели по три столбца с курсом каждой валюты к доллару:

- НАЗВАНИЕ ВАЛЮТЫ
- ДАТА ЗАКРЫТИЯ
- ЦЕНА

2) Как вывести, например, курс арабского дирхама за 02/09? Таблица с примером

Используем VLOOKUP по нужной паре столбцов (даты и значения) именно для AED.

={
 "Rate";
 LET(
  _rows;A2:B20; _cur;Currency!A4:Y; _head;INDEX(_cur;1;);
  BYROW(_rows;LAMBDA(r;
    LET(
      _d;INDEX(r;1;1); _c;INDEX(r;1;2);
      IF(_c="";;
        LET(
          _n;MATCH(_c;_head;0);
          IFNA(
            VLOOKUP(_d; CHOOSECOLS(_cur; _n+1; _n+2); 2; 1)
          )))))))
}

Рассказали в видео, как работает формула и коротко о логике:

_rows— ваши исходные пары (дата; валюта).

_cur— таблица на листе Currency (с заголовками кодов валют в первой строке диапазона).

_head— эта самая строка заголовков, по ней ищем смещение валюты.

Для каждой строки (BYROW): берём дату _d и код _c.
Если код пуст — возвращаем пусто. Иначе находим позицию валюты _n в заголовке и через CHOOSECOLS собираем нужную пару столбцов (дату и значение) именно для этой валюты, после чего VLOOKUP вытягивает курс по дате. IFNA скрывает #N/A, если дата не найдена.

В VLOOKUP(...; ...; 2; 1) последний аргумент 1 — точное совпадение или ближайшее меньшее

⚡️ Друзья, напоминаем про распродажу курсов от маэстро Рената Шагабудинова:

💬 Вопрос от Туко из нашего чата:
Как добавить к результату QUERY пустую строку — в начало или в конец?

Ответ:
Используем функцию VSTACK, которая объединяет массивы данных вертикально.
Чтобы добавить пустую строку в начало, оставляем первый аргумент пустым, а во втором указываем нашу QUERY.

Если оставить первый диапазон пустым, то в результате появится одна ячейка сверху, а остальные будут заполнены значениями #N/A.
Чтобы убрать #N/A, обернём всё в IFNA без второго аргумента.

=IFNA(VSTACK(; QUERY(A1:B7;"select Col1, sum(Col2) group by Col1";0)))

Чтобы добавить пустую строку в конец - просто поменяйте аргументы VSTACK местами:

=IFNA(VSTACK(QUERY(A1:B7; "select Col1, sum(Col2) group by Col1"; 0); ))

VSTACK и HSTACK — мощные функции для объединения диапазонов.
Если ещё не пробовали — самое время 😊.

🚀 trimRange. Ускоряем обработку открытых диапазонов (пост от Алексея Одиссея)

Google Таблицы хорошо оптимизированы под формулы массивов. Поэтому мы часто используем открытые диапазоны вроде A:A или A:C, чтобы не задумываться, как будут разрастаться данные.

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

🎯 Сегодня покажем приём, который помогает ускорить таблицы: обрезаем диапазон (или массив) до последней НЕпустой строки.

Мы будем обрезать диапазон (или массив) до последней НЕ пустой строки.

=ArrayFormula(LET(
  ref; A:C;
  lastRow; MAX(IFNA(byCol(ref; LAMBDA(col; MATCH({"zzz";1E+100}; col)))));
  IFERROR(OFFSET(ref;;;lastRow); ARRAY_CONSTRAIN(ref; lastRow; COLUMNS(ref)))
))

❓КАК ЭТО РАБОТАЕТ смотри под катом ⤵️

💡MATCH({"zzz"; 1E+100}; col) - это "сердце" формулы.
Т.к. вся формула у нас обёрнута в ArrayFormula, то все функции в т.ч. и MATCH будут работать как формулы массива. Поэтому фактически, данная часть будет выполнена 2 раза:
1. MATCH("zzz"; col) - т.к. 3-й аргумент функции опущен, то по-умолчанию search_type = 1, т.е. MATCH ищет позицию наибольшего элемента в массиве col, который меньше (или равен) "zzz". Почему именно "zzz"? Это такой трюк! С точки зрения лексикографического анализа, "zzz" — это очень «большая» строка! Все слова, которые не начинаются на "zzz", будут лексикографически меньше.
2. MATCH(1E+100; col) - аналогично поиску текста выше, но на этот раз мы ищем числа. Чтобы найти любые числа, мы задаём очень большое число - 1 ГУГОЛ ! Поэтому если вы не занимаетесь космологией и теоретической физикой, то величины больше вряд ли встретятся у вас в таблицах. Для справки, число атомов во Вселенной оценивается как 10^80 ;)

💡byCol(ref; LAMBDA(col; MATCH(...)) - в этой части мы проходимся по каждому столбцу диапазона(или массива) ref и находим наибольшие позиции

💡lastRow; MAX(IFNA(byCol(...))); - т.к. в наших данных не всегда есть И текст И числа, подавляем ошибки #N/A, а затем находим наибольшее число по всем столбцам. Результат обозначаем как lastRow в рамкам адресного пространства имен функции LET

💡OFFSET(ref;;;lastRow) - здесь мы указываем наш диапазон и 4-й аргумент, высоту возвращаемого диапазона, фактически обрезая его.

💡 IFERROR(OFFSET(...); ARRAY_CONSTRAIN(ref; lastRow; COLUMNS(ref)))
На случай, если ref является массивом, а не диапазоном, а такое возможно, например, если он получен в результате каких-то действий на предыдущих шагах в рамках функции LET, OFFSET(...) выдаст ошибку, и будет выполнена функция во втором аргументе IFERROR - ARRAY_CONSTRAIN. Эта функция как раз и сократит массив до вычисленной строки lastRow.

🌟Мы используем эту логику в большинстве рабочих кейсов и вынесли её в именованную функцию TRIMRANGE, чтобы Вы могли легко импортировать её.
❗️Пример таблицы

✅ Записывайтесь на курс "Скрипты и Формулы в Google Таблицах", где мы разбираем подобные кейсы 😉