Функции баз данных

Функции БД - мощный инструмент. Они есть и в Excel и в Google Таблицах и хороши для работы с несколькими условиями, с наборами условий.

Подготовили для вас статью про эти функции и про то, как ими пользоваться.

Наглядно и с примерами: https://teletype.in/@renat_shagabutdinov/4lVaI_Pj7

Флаг вам в руки — суммируйте что хотите. SUMIFS с флажком

Задача: вставить в таблицу флажок, который будет включать или отключать один из критериев функции СУММЕСЛИМН / SUMIFS (ну или ее аналогов для подсчета/усреднения COUNTIFS, AVERAGEIFS, COUNTUNIQUEIFS).

Вставляем флажок и добавляем функцию IF / ЕСЛИ, которая в соответствующем аргументе будет возвращать условие, если флажок включен, и “*”, если флажок выключен. Так как звездочка — символ подстановки (wildcard), соответствующий любому тексту, то одна звездочка = любое значение = отсутствие всякого условия для соответствующего столбца.

=SUMIFS(F:F;B:B;IF(I4;J4;"*"))

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

=SUMIFS(F:F;B:B;IF(I4;"*"&J4&"*";"*"))

Понятное дело, таким образом и несколько флажков можно сделать для разных критериев — как в примере по ссылке (создать копию).

Где еще работают звездочки? В функциях VLOOKUP / ВПР, MATCH /ПОИСКПОЗ, XLOOKUP / ПРОСМОТРX и XMATCH / ПОИСКПОЗX (но там нужно включить поиск с символами подстановки, задав аргумент match_mode равным двойке), в функции SEARCH/ ПОИСК , в функциях баз данных (DSUM / БДСУММ, DAVERAGE / ДСРЗНАЧ , DCOUNT / БСЧЁТ, DCOUNTA / БСЧЁТА и других).
В Excel — еще и в фильтрах, условном форматировании, окне "Найти и заменить".

Выделяем строки с топ-N значений в каком-то столбце

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

=ссылка на первую ячейку в столбце с проверяемыми числами >= LARGE(столбец с числами; число первых N значений)

Функция LARGE / НАИБОЛЬШИЙ вычислит N-ное значение в диапазоне. Например, если вторым аргументом мы ей дадим 5 (в самой формуле или в ячейке, чтобы проще было менять это число потом), то она вернет 5 по порядку значение из столбца с числами. И мы выделим все строки, в которых числа в нужном столбце будут больше этого значения (включительно).

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

Или, в нашем примере:

=$F2>=LARGE($F$2:$F;$J$1)

В столбце F числа, по которым мы выделяем самые крупные сделки, в J1 число (сколько крупнейших строк выделяем).

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

Наконец-то в таблицах Google появятся таблицы!

Речь про аналог того, что в Excel называется "Таблицами" (Tables; про них целая книга есть, между прочим), в русскоязычном сообществе и книгах — "умными таблицами", а в Google Spreadsheets будет тоже Tables.

— В таблицах форматирование автоматически распространяется на будущие строки
— Можно ссылаться на столбцы таблицы вот так: Имя_Таблицы[Имя_Столбца] (и тогда все будущие строки этого столбца будут учтены в вашей формуле, да и читабельность получше
— У столбцов можно будет устанавливать тип данных (например, дата или текст или выпадающий список) — такая проверка данных, которая встроена в таблицу и тоже будет автоматом распространяться на добавляемые строки
— Будет новый тип представления — Group View, временная группировка по одному из столбцов таблицы.

Ждем! Как только появятся, расскажем подробнее и про ссылки на таблицы в формулах, и про прочее. Новость тут (гифка оттуда же):
https://workspaceupdates.googleblog.com/2024/05/tables-in-google-sheets.html

СОХРАНЯТОР: берем ссылки и делаем из них файлы на Google Диске из Таблиц

Друзья, привет!

Недавно мы написали для вас о том, как в Таблицах создавать QR-коды. В комментариях к этому посту был вопрос - а как сохранить созданные картинки (имея ссылку на них) как файлы на Google Диске?

Все время после того, как мы получили вопрос мы не спали, не ели и думали над решением, в итоге оно готово, мы создали для вас Таблицу со скриптом.

Таблица со скриптом
1) в ячейку A2 вставляете ID папки на Диске (Все будет сохраняться в неё)
2) в диапазон A5:A вставляете прямые ссылки на свои файлы
3) запускаете скрипт через клик на картинку
4) скрипт обойдет каждую ссылку и попытается создать из нее файл и сохранить этот файл на Диск в вашу папку, в случае успеха поместит ссылку на результат в столбец B, в случае ошибки - эту ошибку

PS Картинки сохраняются, PDF-файлы также сохраняются, остальное не пробовали, потестируйте самостоятельно.

Код отдельно

function google_sheets() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Сейв!');
  const d = sh.getDataRange().getValues();
  var folder = DriveApp.getFolderById(d[1][0]);

  if (!folder) {
    ss.toast('папка не существует или у вас к ней нет доступа, в общем, всё');
    return;
  }

  for (var x = 4; x < d.length; x++) {
    try {
      const url = d[x][0];
      const response = UrlFetchApp.fetch(url).getBlob();
      const file = folder.createFile(response);
      const fileUrl = file.getUrl();
      sh.getRange(x + 1, 2).setValue(fileUrl);
    } catch (err) {
      sh.getRange(x + 1, 2).setValue(err.name + ', ' + err.message);
    }
  };
};

---
⭐️ Заказ работы у нас

ДВИГАЕМ ВРЕМЯ ФОРМУЛАМИ

– как получить дату следующего понедельника
– как перейти на два месяца назад
– как прибавить три часа
– как отбросить время и оставить только дату
– как округлить время до часа

И подобные кейсы в прекрасном справочнике от участника нашего чата Михаила Смирнова, спасибо ему 👏.

На скриншоте – не всё, смотрите Таблицу.

==
Еще полезное про даты:
> Выводим ряд чисел или дат. Одной формулой.
> Сегодня хороший день - 43 873.

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Возвращаем ссылку на диапазон с помощью функций

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

Например, мы можем находить значение с помощью сочетания INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ. Если после поставить двоеточие, то вместо значения будет возвращаться ссылка на ячейку.

В следующем варианте ищем два значения - превращаем каждое из значения в ссылку за счет двоеточия, получаем на выходе диапазон и его суммируем

=SUM(INDEX(диапазон суммирования;MATCH(что ищем-1;диапазон поиска;0)):INDEX(диапазон суммирования;MATCH(что ищем-2;диапазон поиска;0)))

Магия работает в Excel и Google Таблицах со следующими функциями:
CHOOSE / ВЫБОР
IF / ЕСЛИ
IFS / ЕСЛИМН
INDEX / ИНДЕКС
INDIRECT / ДВССЫЛ
OFFSET / СМЕЩ
SWITCH / ПЕРЕКЛЮЧ
VLOOKUP, HLOOKUP, LOOKUP / ВПР, ГПР, ПРОСМОТР
XLOOKUP / ПРОСМОТРX (удовольствие, доступное в Microsoft 365 / Excel 2021)

INDEX будет работать в качестве ссылки и без двоеточия - тогда это будет ссылка на одну ячейку. Если он будет указан в аргументе, тип которого - ссылка. Пример в таблице.

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

Вычисляем номер недели в рамках месяца

Функция НОМНЕДЕЛИ / WEEKNUM возвращает номер недели в рамках года (напоминаем, что у нее, как и у ДЕНЬНЕД / WEEKDAY есть второй аргумент "Тип", и если его не указать, первым днем недели будет воскресенье; для привычной нам недели с понедельника нужно указать тип = 2)

Ну а для номера недели в рамках ее месяца вычтем номер недели, соответствующий дате ,номер недели для первого числа месяца:
=Номер недели для заданной даты - номер недели для начала месяца + 1

Начало месяца для заданной даты можно получить как:
=DATE(YEAR(дата); MONTH(дата); 1)
или
=EOMONTH (дата; -1) + 1

Все вместе получится:
=WEEKNUM(дата; 2) - WEEKNUM(DATE(YEAR(дата); MONTH(дата); 1); 2) + 1

(добавляем единицу, чтобы для первой недели месяца получался не 0, а 1)

Проверьте, не появились ли у вас таблицы в Таблицах!

Формат — Преобразовать в таблицу
Format — Convert to table
Alt+O + E

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

Ссылки в формулах на таблицы выглядят так (название таблицы, которое используется в формулах, можно поменять в левом верхнем углу таблицы; в примере используем название "Сделки"):

Сделки — все данные без заголовков
Сделки[#All] — все данные с заголовками
Сделки[Название_столбца] — все данные в определенном столбце, без заголовков.

Прелесть этих ссылок в том, что при добавлении/удалении строк в таблице они будут актуальны, это будут все строки в таблице/столбце на данный момент.

P.S. При скачивании таблицы на локальный диск в формате .xlsx таблицы откроются в Excel (там такие есть уже много лет)

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

Алгоритм такой:
1. Убираем заголовки с месяцами (чтобы остались только дни; нам не нужно считать итог месяца отдельным днем, его надо пропустить) через функцию FILTER. Можно исключать конкретное слово "<>итого" или оставлять только числа ISNUMBER(...) )
2. Далее в полученном массиве идущих подряд дней с помощью SCAN считаем нарастающий итог — число идущих подряд единиц. Если есть единица, прибавляем к накопленному итогу ее, иначе обнуляем счетчик)
3. В результате получаем массив с накопленными днями отпусков — с помощью COUNTIF проверяем, есть ли там хоть одно число от 14 включительно.
4. Напоминаем вам, что логические значения TRUE и FALSE, если они выдаются формулами, можно показывать как флажки.

Вариант для одной строки (на скриншоте в строках с 9-й видно, что возвращает функция SCAN, то есть все, что внутри COUNTIF / СЧЁТЕСЛИ) — массив накопленных значений, в котором мы потом ищем числа от 14.

=COUNTIF(SCAN(0;FILTER(строка с днями сотрудника;строка заголовков<>"итого"); LAMBDA(acc;value;IF(value;acc+value;0)));">=14")<>0

Вариант от Игоря Дроздова (спасибо!) — одной формулой все сотрудники:

=BYROW(SCAN(;IFNA(HSTACK(;FILTER(D3:NQ7;ISNUMBER(D2:NQ2))));LAMBDA(acc;z;IF(z="";;acc)+z));LAMBDA(zz;IF(MAX(zz)>13;TRUE;FALSE)))

Ссылка на таблицу с вариантами формул

Не забывайте отдыхать, друзья! И предлагайте свои варианты решения задачи🤠

Друзья, привет

Достаём и показываем вам простой скриптовый кейс, который мы сделали для наших заказчиков на днях.

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

Вот вам Таблица со скриптом, можете сделать копию и посмотреть, как все устроено. Открываете редактор скриптов и видите там справочник (он называется "O"), в нем ссылки на диапазоны и формулы, которые в эти диапазоны будут вставлены при запуске скрипта. Конечно, именно эти формулы вам не нужны, да и вряд ли у вас есть листы с такими названиями, но когда у вас будет похожая задача - то вы сможете адаптировать код (просто переделав справочник) под себя.

Таблицы, с которыми будет работать код - на листе "список", скрипт запускаете по клику на зеленую плашку, скрипт продолжит с первой таблицы, по которой не будет заполнена дата и время обработки в столбце B.

Многоуровневая нумерация списка

Еще одна задача от участников практикума по магии формул: сделать нумерацию с тремя уровнями одной формулой.

Можно так:
1. Отправляем все столбцы с уровнями в MAP
2. Для первого столбца получаем массив без повторов с помощью UNIQUE и функцией MATCH / ПОИСКПОЗ ищем позицию каждого значения в этом массиве (то есть первый элемент («Детская одежда») будет первым для всех его вхождений (первые пять строк таблицы)
3. Для последующих уровней сначала фильтруем (FILTER) значения, оставляя только соответствующие текущему значению более высокого уровня (для женской одежды оставляем только две категории второго уровня — «для высоких» и «для невысоких» и в рамках этого массива вычисляем порядковый номер каждого значения в столбце с Категорией 2.
4. Добавляем точки и склеиваем все вместе через &.

Как всегда, будем рады вашим вариантам решения в комментариях!

Здравствуйте, уважаемые любители формул в Google Таблицах

Недавно мне написала подписчица нашего канала и задала два вопроса, отправив Таблицу с примером. Отвечу на оба вопроса и покажу вам, что получилось.

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

Вопрос 1.
У нас есть длинная строка с назовём это "наименованием платежа", напишем формулу, которая вытащит из строки название компании. Название компании у нас идёт после COMPANY:

Напишем сразу формулу массива, ибо так пожелала наша подписчица:

=ARRAYFORMULA(IFNA( REGEXEXTRACT(B3:B18;""COMPANY:([^ ]+)"")))

Что происходит? C помощью REGEXEXTRACT с регулярным выражением COMPANY:([^ ]+) достаём из строки строки всё, что после COMPANY: и до первого пробела. Поверх добавляем IFNA, чтобы скрыть ошибку, если ничего не достанется. И на верхнем уровне у нас ARRAYFORMULA, которая заставляет всё это заработать в массиве. Некоторые эстеты из нашего чата (Михаил, Игорь, привет) воспользовались бы вместо ARRAYFORMULA INDEX-ом, винить мы их в этом не будем 🙂

Вопрос 2.
Выведем сумму из C:C, если в B:B у нас встречается слово wire, сделаем это также в формуле массива

=ARRAYFORMULA( IF(REGEXMATCH(B3:B19;""(?i)wire"");C3:C19;))

Ну тут все попроще, с помощью REGEXMATCH с регулярным выражением (?i)wire проверяем ячейку на наличие слова wire, а с помощью IF / ЕСЛИ с ARRAYFORMULA возвращаем либо сумму из C:C, если проверка проверила и нашла то, что слово входит в строку, либо пустоту, если слово в строку не входит (REGEXMATCH вернул FALSE).

* (?i) в регулярке обозначает написание в любом регистре

Материалы:
Компактная памятка про регулярки от Vitalich
"Народная" база с примерами регулярных выражений (внутри многое плохо, народная же, но некоторое понимание получите)

Функция SCAN: нарастающий итог — простой, по каждому году/месяцу или с условием

SCAN — одна из вспомогательных функций LAMBDA, которая позволяет пробегаться по массиву, обращаясь к каждому элементу и накопленному итогу. И творить всякую магию. Доступно это удовольствие в Google Таблицах и в Excel 365 / Excel Online.

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

Скриншоты в статье из Excel, но работает все аналогично.
Вот Google Таблица с примерами (создать копию)

Решили чуть перевести справку по функции SORTN на человеческий язык для вас

Эта функция похожа на SORT, но в ней можно выводить N первых значений, а не все (N задается во втором аргументе). И есть также третий аргумент — режим показа совпадений — показываем, как будет вести себя функция для каждого из вариантов.

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

В Excel с динамическими массивами есть немного другая функция СОРТПО / SORTBY. Тоже задаем столбец или столбцы для сортировки отдельно как диапазоны, а не номерами, как в функции SORT. Но аргументов N (этот аргумент может заменить функция ВЗЯТЬ / TAKE) и режима показа совпадений нет.

Скрипт для Таблицы. Переносим значения из ячеек в примечания к ячейкам.

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

Что делает скрипт?
1. Берёт все значения "лист1";
2. Вставляет значения в примечания к ячейкам (если в примечаниях что-то есть, то скрипт добавит данные);
3. Далее очищает лист от всех видимых значений, оставляет только примечания;

Код отдельно

function Move() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Лист1');
  var range = sheet.getDataRange();
  var values = range.getValues();
  var notes = range.getNotes();

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j]) {
        if (notes[i][j]) {
          notes[i][j] += '\n' + values[i][j];
        } else {
          notes[i][j] = values[i][j];
        };
      };
    };
  };

  range.clearContent();
  range.setNotes(notes);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Menu')
    .addItem('Move', 'Move')
    .addToUi();
}

Таблица со скриптом

Наконец-таки полезное! (возможно скажете вы, наши читатели)

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

Чтобы добыть комментарии:
1) копируем Таблицу себе
2) вставляем в Комментарии!A1 айди нашего документа
3) запускаем единственный скрипт из меню

Код отдельно, чтобы код работал в проекте нужно включить Drive Api

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('@google_sheets')
    .addItem('⤵️', 'extract')
    .addToUi();
}


function extract() {
  var ss = SpreadsheetApp.getActive();
  var shLog = ss.getSheetByName('Комментарии');
  var pageToken = 'None';
  var arr = [];
  do {
    c = Drive.Comments.list(shLog.getRange("A1").getValue(),
      { 'pageToken': pageToken, fields: "*" });

    c = JSON.parse(c);

    c['comments'].forEach(f => {
      arr = arr.concat([[
        'комментарий',
        f.htmlContent,
        f.deleted,
        new Date(f.modifiedTime),
        f.author.displayName]]);

      f.replies.forEach(ff => {
        arr = arr.concat([[
          'реплай',
          ff.htmlContent,
          ff.deleted,
          new Date(ff.modifiedTime),
          ff.author.displayName]]);
      });
    });

    pageToken = c.nextPageToken;
  } while (pageToken);

  if (arr.length) {

    arr = [['~', 'htmlContent', 'deleted?', ' modifiedTime', 'author']].concat(arr);
    shLog.getRange("A2:E").clearContent();
    shLog.getRange(2, 1, arr.length, arr[0].length).setValues(arr);

  };
}

К google-таблицам добавляем SQL и наблюдаем, как растут цифры в графе «зарплата» на работных сайтах.

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

В этот вторник приходите на бесплатный мастер-класс с Серафимом Фролкиным, инженером данных VK и экспертом программ школы аналитики Changellenge >> Education.

Серафим научит писать и читать SQL-код и проследит, чтобы все участники во время эфира решили свой первый кейс.

Справятся даже новички. Всего полтора часа — и рядом с google-таблицами в резюме настоящий аналитический навык.

Дата: 25 июня
Время: 19:00 Мск
Формат: онлайн
Длительность: 1,5 часа
Стоимость: бесплатно
Карьерные бонусы: всем зарегистрировавшимся и участникам!

Регистрируйтесь и получайте подарки >>

Реклама. ООО «Высшая школа аналитики и стратегии». ИНН 7716917009. erid: 2Vtzqua7EBR

И вообще зачем нам читать комментарии, если мы их можем удалить

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

⤵️ - Скрипт извлекает все комментарии из документа, ID которого вы укажете в ячейке A1.

del - Скрипт удаляет отмеченные флажком (колонка G на скриншоте) комментарии из документа.

Визуально то, что комментарий именно удалён вы обнаружите после перезагрузки страницы с документом.

Код удаления отдельно

function deleteComments() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Комментарии');
  var d = sh.getDataRange().getValues();
  var id = d[0][0];


  d.forEach((row, i) => {
    if (row[6] == true) {
      try {
        Drive.Comments.remove(id, row[5]);
      } catch (err) { };
      sh.getRange(i + 1, 7).clearContent();
    };
  });
};

---
⭐️ Заказ работы у нас