Пользовательская сортировка

Вам нужно сортировать данные в своем порядке — не по алфавиту.
Например, какой-то регион в вашем бизнесе важнее других, хотя название города не первое по алфавиту.
Или речь про размеры одежды S, M, L...

Как быть? Можно создать дополнительный столбец с нумерацией.
Заполнять его будем формулой, в общем виде так:

=XMATCH (ссылка на ячейку со значением; {массив всех вариантов в нужном порядке})

В случае с одеждой так:

=XMATCH(B2;{"S";"M";"L";"XL"})

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

Не хочется дополнительный столбец? Можно сразу сделать значения вида "1 S", "2 M" или "1-S" и так далее (сначала номер для сортировки и потом само название элемента). Но такой трюк сработает только при числе элементов до 10. Потому что если их больше, то данные будут сортироваться так: 1-текст, 10-текст, 11... 19, 2, 20, 21...

📌 А в следующем посте покажем, как добавить этот столбец как виртуальный в функцию SORT и отсортировать данные

Как сделать бекап Google Диска

1. Самый простой и правильный способ забекапить данные
https://takeout.google.com/

2. А также – нажатие на кнопку Download / Скачать в контекстном меню по щелчку на папке Google Диска создаёт ZIP-архив с файлами (при этом файлы редакторов Google будут преобразованы в форматы Microsoft Office, например, Google Таблицы в XLSX) и скачивает его на ваш компьютер.

3. Вот здесь мы писали о скрипте, с помощью которого можно сохранить лист Таблицы в выбранном формате (PDF / CSV / XLSX).

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

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

Рассчитываемое поле не работает в сводной таблице?

Проверьте формат заголовков.
Если вы применили формат "Расчеты" (Accounting) не только к числам, но до кучи в заголовках, то в ячейках появится отступ. Выглядит как пробел, но вы не найдете его в значении — ни в строке формул, ни текстовыми функциями ДЛСТР / LEN или ЛЕВСИМВ / RIGHT, он себя никак не проявит.

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

Или, что будет правильнее, не поменяете формат в ячейках с заголовками на "Обычный текст" (Plain text) или "Автоматический" (Automatic).

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

Друзья, к нам приходит выписка вот в таком формате:

26 Марта
Пополнение
#173/Лицевой счет
20000.00 ₽
26 Марта
Пополнение
#1730/Лицевой счет
50000.00 ₽
25 Марта
Пополнение
#1726/Лицевой счет
25000.00 ₽

Как бы нам из этого получить нормальную Таблицу, со строками и столбцами?

Поехали получать:

1) Сначала разделим наш длинный текст по переносу строки (символ char(10)), с этим поможет формула
=SPLIT(A1;char(10))

2) Получаем строки, в каждой строке у нас четыре элемента (дата, суть платежа, наименование и сумма) , чтобы каждую строку разложить на эти элементы, используем функцию WRAPROWS (подробнее про функции работы с массивами), а в аргументах будет полученный массив строк и на сколько частей каждую строку нужно разделить:
=WRAPROWS(SPLIT(A1;char(10));4)

Ура, все вышло

Получаем из длинной строки Таблицу (часть 2)

Ребята, усложняем наш пример, в реальности в наших данных под каждой датой может быть несколько платежей (смотрите, обвёл эти случаи на скриншоте).

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

function myFunction(arg) {
//делим на строки по " ₽" и переносу строки
  var first = arg.split(" ₽\n");

//каждую часть еще раз делим по переносу строки и проверяем, сколько получилось элементов, если четыре – всё хорошо, если три - даты нет, вместо даты подставляет пустую ячейку
  var second = first.map(row => {
    row = row.split("\n");
    if (row[3]) {
      return row;
    } else {
      return [''].concat(row);
    };
  });

//пробегаемся в цикле по массиву, по каждой строке и если дата нет, то берем ее из строки выше
  var third = second.map((row, i) => {
    if (!row[0]) {
      row[0] = second[i - 1][0];
    }
    return row;
  })

//возвращаем результат
  return third;
};

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

PS В следующем посте покажем вам, как парсить страницу с товаром так, чтобы получать и категории и описание товара

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

Удаляем лишние пробелы

Для этого можно использовать функцию TRIM / СЖПРОБЕЛЫ — она удаляет пробелы в начале строки (все до первого слова), в конце и оставляет только по одному пробелу между словами.

А если нужно удалять переносы строк, воспользуйтесь CLEAN / ПЕЧСИМВ — эта функция удаляет непечатаемые символы (первый 31 символ ASCII, в том числе и перенос строки, с которым мы можем столкнуться в Таблицах).

Обе функции есть и в Excel.

А в Таблицах еще есть инструмент для удаления пробелов без формул, если вам нужно сделать это разово (без пересчета).

Данные — Очистка данных — Удалить пробелы
Data — Data cleanup — Trim whitespace

Или клавиши: (Alt+D) + U + I

Вычисляем первый и последний рабочие дни месяца

Первый день вычислим так: найдем последнюю дату предыдущего месяца (КОНМЕСЯЦА / EOMONTH с аргументом -1, то есть на 1 месяц раньше от заданной даты) и прибавим один рабочий день с помощью РАБДЕНЬ / WORKDAY:
=WORKDAY(EOMONTH(дата;-1);1)

Ну а последний рабочий день месяца — это первый день следующего месяца (= конец текущего плюс один день), из которого мы вычитаем один рабочий день:
=WORKDAY(EOMONTH(дата;0)+1;-1)

Обводим данные при открытии Таблицы

Друзья, сегодня у нас очень простой скрипт, идея скрипта возникла у нас в чате (Вероника, спасибо ❤️)

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

function onOpen() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Лист');
  const dr = sh.getDataRange();
  dr.setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.DOTTED);
};

2) Дополнение, по просьбам трудящихся, делаем границы на всех листах Таблицы при открытии:

function onOpen() {
  const sheets = SpreadsheetApp.getActive().getSheets();

  sheets.forEach(sh => {
    const dr = sh.getDataRange();
    dr.setBorder(true, true, true, true, true, true,
      '#000000',
      SpreadsheetApp.BorderStyle.DOTTED);
  })
};

Таблица с кодами

Перевод строки: как разделить по нему текст или очистить текст от него в Google Таблицах и Excel

Сегодня говорим про СИМВОЛ(10), он же Alt+Enter, он же перевод строки:
- Как разбить текст из одной ячейки, в которой много строк, на отдельные столбцы
- Как удалить переходы на новую строку из всех ячеек диапазона

https://teletype.in/@renat_shagabutdinov/arny9pYwYld

Достаём характеристики и описание товаров из ВБ, Таблица со скриптом

Друзья, привет! Делимся с вами Таблицей, пользуйтесь, пока работает.

Просто копируйте Таблицу к себе, вставляйте номенклатуры в первый столбец и запускайте скрипт из меню с "🐱". Таблица загружает всё, что на скриншоте.

В скрипте две части:

1) сначала формируем исходя из номера товара (того самого номера, который вы видите в ссылке на товар в ВБ) ссылку на JSON (ссылка, например)

2) загружаем этот JSON по каждому товару и достаём из него составные части (название категории, сезон, опции), результат вставляем на лист Таблицы

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

Любимый ВБ, формируем ссылки на изображения товара

---
⭐️ Мы создаём разные полезные решения для ОЗОН и ВБ, пишите в заказ работы

Обводим 2

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

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

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

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

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

Код отдельно:

function onOpen() {
  const ss = SpreadsheetApp.getActive();
  const ranges = ["Лист!a3:d", "Лист!h2:i", "Лист!l2:n"]

  ranges.forEach(range => {
    const data = ss.getRange(range).getValues();
    var lr = 0;
    data.forEach((row, i) => {
      if (row.some(f => f)) {
        lr = i;
      };
    });

    const real_lr = range.match(/\!\D(\d+)/)?.[1] * 1 + lr
    ss.getRange(range + real_lr)
    .setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.DOTTED)
    .setBackground('pink');
  })
};

---
⭐️ Заказ работы у нас (по ссылке - примеры)

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

Функции БД - мощный инструмент. Они есть и в 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)