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

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

Можно так:
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);

  };
}

Извлекаем комментарии из документа / Удаляем комментарии / xlsx-файл - это архив / достаём из XML комментарии и адреса

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

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

⤵️ - Скрипт извлекает все комментарии из документа, 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();
    };
  });
};

Извлекаем комментарии из документа / Удаляем комментарии / xlsx-файл - это архив / достаём из XML комментарии и адреса

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

Строим воронку с помощью спарклайнов

Для построения воронки специальная одноименная диаграмма есть в Excel — но только в одной из последних версий, так что проблема совместимости встанет в полный рост.

В Таблицах же мы можем построить воронку с помощью спарклайнов!

Алгоритм такой:
1 Вычисляем отношение очередного этапа к первому. Для первого этапа это априори будет единица.

2 Считаем, сколько остается до единицы, чтобы сформировать массив из трех значений для будущего спарклайна типа bar (а в нем, напомним, может быть не только одно или два значения). Допустим, второй этап — это 50% от первого (половина клиентов ушли на этом этапе). Значит, нам нужен будет массив значений 25% — 50% — 25% (при этом цвет для 25% будет белым, таким образом, у нас будет спарклайн в половину ширины столбца, то есть первого этапа, и посередине ячейки).

3 Засунем все это добро в MAP и LAMBDA, чтобы работало для разного числа этапов и одной формулой.

Формула получится такой (вариантов тут может быть много):

=MAP(массив ;LAMBDA(x;
  SPARKLINE(
  HSTACK((1 - x / первое значение) / 2 ;
                   x / первое значение ; 
                  (1- x / первое значение) / 2 ) ; 
           {"charttype" \ "bar" ; "color1" \ "white"})))

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

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

Таблицы теперь должны работать быстрее (удвоена скорость вычислений в браузерах Chrome и Edge)

https://workspaceupdates.googleblog.com/2024/06/improving-calculation-speed-in-google-sheets.html

А вы заметили у себя? Напишите в комментариях.

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

Прогресс-бар (выполнение плана) спарклайном

Динамический спарклайн: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет - на русском языке)

Спарклайн с условием

Видеоурок по спарклайнам: синтаксис и примеры

Превращаем строку с числами в спарклайн одной формулой

Спарклайны. Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.

Вышеобещанное – достаём из XML с комментариями текст комментария и адрес ячейки, в которой он расположен, с помощью Google Apps Script

Что делать

Скопировали xml, вставили в бэктиках (`) в редактор скриптов, назвали переменную xmlString

Скопировали туда же код скрипта, который найдете ниже

Запустили и получаем перечисление в цикле каждого комментария и его адреса (смотрите картинку)
 
Один XML-файл с комментариями внутри XLSX архива соответствует одному листу Таблицы

const xmlString = ``

function parseXML() {
  var o = {};
  var document = XmlService.parse(xmlString);
  var root = document.getRootElement();
  var ns = XmlService.getNamespace('http://schemas.openxmlformats.org/spreadsheetml/2006/main');
  var comments = root.getChild('commentList', ns).getChildren('comment', ns);

  for (var i = 0; i < comments.length; i++) {
    var comment = comments[i];
    var ref = comment.getAttribute('ref').getValue();

    var textElement = comment.getChild('text', ns);
    var tElement = textElement.getChild('t', ns);
    var textContent = tElement.getText();

    console.log(ref, textContent);
  };
};

А в следующий раз мы отправим комментарии на луну :)

Извлекаем комментарии из документа / Удаляем комментарии / xlsx-файл - это архив / достаём из XML комментарии и адреса

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

Статья в первую очередь для тех, кто работает в Р7-Офис. Но остальным будет полезно пробежаться и вспомнить про принципы работы формул массива в Google Таблицах / Excel и узнать, как все устроено в российском табличном редакторе :)

А там своеобразное сочетание — есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами и в Таблицах тоже недавно. При этом формулы массива в Р7 работают как “старые” формулы массива Excel (возможно, это когда-нибудь изменится — очень надеемся). Так что новыми функциями пользоваться не так удобно, как в Таблицах или новом Excel, но зато они в принципе есть🙂

В статье разбираем:
— Как вводятся и работают старые и новые формулы массива в Excel
— Какие функции из новых доступны в Р7
— И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :)

https://shagabutdinov.ru/r7array/

Копируем таблицу от имени владельца, скрипт

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

Зачем это нужно? Чтобы сохранить все защиты диапазонов, которые есть в Таблице, они должны быть также от имени владельца. А также, чтобы Таблица лежала на Google Диске владельца.

--

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

Как все работает:

1) Скопировали нашу Таблицу

2) Далее владелец Таблицы, которую мы будем копировать должен зайти в редактор скриптов и поставить скрипт on() на триггер по редактированию Таблицы

(триггеры > добавление нового триггера > выбираем функцию on() и выбираем "при редактировании"

2) Далее "сотрудники" заполняют лист (ссылка на Таблицу, кого добавим как редакторов в копию и новое имя Таблицы)

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

И отдельно код:

function on(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;

  if (sheet.getName() == 'настройки' && range.getA1Notation() == 'B4' && e.value == 'TRUE') {
    copy();
  };
};

function copy() {
  const ss = SpreadsheetApp.getActive();
  const settings = ss.getRange("настройки!b1:b3").getValues().flat();
  var [originalUrl, addEmails, name] = settings;
  if (!originalUrl) return;
  var newUrl = '';
  try {
    const or = SpreadsheetApp.openByUrl(originalUrl);
    newUrl = or.copy(name || 'копия, ' + or.getName());
    if (addEmails) newUrl.addEditors(addEmails.split(',').map(f => f.trim()));
    newUrl = newUrl.getUrl();
  } catch (e) {
    newUrl = e.message;
  };

  ss.getRange("настройки!b5:c5")
    .setValues([[newUrl, new Date()]]);
};

Видео для новичков-новичков: "умные" таблицы

Друзья, для тех, кому удобнее формат видео, ролик про таблицы (tables) в Google Spreadsheets:
https://www.youtube.com/watch?v=Pn_b_mpr__k

Ранее мы писали про основные опции таблиц и ссылки на данные в них тут

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

В чем преимущества?
— Новые строки будут автоматом попадать в сводную
— Пустые строки не будут попадать в нее и их не придется фильтровать (как если бы мы брали в качестве источника столбцы, например, A:F)
— Таблица может начинаться не с первой строки листа и над/под ней может быть что-то еще. А если мы берем столбцы в качестве источника сводной, то в них больше ничего, кроме данных для нее, быть не может.

Форматируем данные как таблицу, выделяем любую ячейку, вставляем сводную —> видим, что в качестве источника фигурирует не диапазон, а имя таблицы (де-факто именованный диапазон, меняющий свои размеры при добавлении новых данных).

Вечная схватка двух ёкодзун: Google Таблицы и Excel

Ну что, друзья, с появлением таблиц в Google Spreadsheets пришло время обновить таблицу-сравнение двух табличных редакторов, а заодно напомнить про нее и рассказать тем, кто пропустил.

Она живет по этой постоянной ссылке:
https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8

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

Как ускорить работу в Google Таблицах: 7 лайфхаков

Статья в РБК для новичков табличных дел! Делюсь 7 лайфхаками для работы в Google Spreadsheets: форматирование, горячие клавиши для вызова команд из меню, работа с ссылками, сортировка, вычисление фрагмента, пользовательские числовые форматы.

https://companies.rbc.ru/news/qnC1EmgtQx/kak-uskorit-rabotu-v-google-tablitsah-7-lajfhakov/

Почему объединение ячеек — это не очень хорошо?

Потому что за объединенной ячейкой всегда скрывается только одна заполненная (левая верхняя, первая, попросту говоря) и все остальные пустые!

А значит, формулами мы это дело не обработаем: в примере ломается ВПР / VLOOKUP, потому что название курса берется только из первой из объединенных ячеек.

Что если вы повторили все названия, но визуально хотите сохранить эффект объединения?

Как вариант — придумать формулу условного форматирования, которая будет "прятать" (применять белый цвет шрифта) все "лишние", нецентральные подписи.

Сортируем данные по фамилии (или другому слову в составе текстовой строки)

Вот такой лайфхак от маэстро табличных дел Бена Коллинса.

Если в ячейках данные формата Имя Фамилия, а нам надо сортировать по фамилии, а не по всей текстовой строке (включая имя), то можно в функцию SORT засунуть текстовые функции, которые будут извлекать фамилию:

=SORT(диапазон ;RIGHT(диапазон ;LEN(диапазон )-SEARCH(" ";диапазон ));1)

Здесь мы определяем, сколько символов в фамилии (находим с помощью SEARCH / ПОИСК или FIND / НАЙТИ разделитель — пробел — и вычитаем его позицию из общего числа символов в строке (LEN / ДЛСТР).
Затем извлекаем соответствующее число символов с конца строки с помощью функции RIGHT / ПРАВСИМВ.

А как это делается в Excel с новыми функциями (в частности, TEXTAFTER / ТЕКСТПОСЛЕ), можно посмотреть здесь:
https://tttttt.me/lemur_excel/273

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