Нумеруем только видимые строки

Задача: хотим, чтобы при скрытии строк нумерация менялась: чтобы номера были по порядку только для видимых в текущий момент строк.

Решение:

=SUBTOTAL(103;$B$2:B2)

(если начало в строке два)

Считаем (103 = СЧЁТЗ / COUNTA) значения в каком-то столбце таблицы, но считаем только видимые (не скрытые), так как используем SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Ссылка вида $B$2:B2 — то есть всегда считаем с начала таблицы до текущей строки (при смещении формулы будет меняться только конец диапазона).

Распознаем текст рассказа О`Генри, взяв PDF из интернета с помощью Таблицы

Друзья, в Google Docs есть технология OCR (оптическое распознавание текста). Мы подготовили для вас Таблицу со скриптом и в видео показали, как она умеет "на лету" распознавать текст из pdf / изображений, ссылку на файл мы взяли прямо из интернета.

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

Чтобы все заработало у вас:

1) Делаем копию Таблицы

2) В ячейку листа "config" вставляем URL файла (пойдет PDF, картинка, ссылка может быть как на Google Диск, так и на файл в интернете);

3) Запускаем скрипт кнопкой "GO!", скрипт создаст из заданного файла новый Google Документ с текстом и вставит весь текст из него на лист "OCR";

4) Хотите сразу удалять временный файл – уберите комментарий (//) с 29 строки кода;

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

Итак, до Google таблиц доезжают ссылки на умные таблицы, которые ранее существовали в Excel.

Изначально с появлением таблиц в Google Spreadsheets можно было ссылаться на все данные в столбце:

Таблица[Столбец]

Теперь как в Excel — можно ссылаться на весь столбец вместе с заголовками и итогами:

Таблица[[#ALL],[Столбец]]

На данные и заголовки, но без итогов:

Таблица[[#HEADERS],[#DATA],[Столбец]]

Можно на итоги или заголовки конкретного столбца

Таблица[[#HEADERS],[Столбец]]
Таблица[[#TOTALS],[Столбец]]

На всю строку заголовков или итогов:

Таблица[#HEADERS]
Таблица[#TOTALS]

И все это актуально для ссылок на несколько столбцов, которые теперь тоже работают в Google. Указываем первый и последний столбец через двоеточие:

Таблица[[Первый]:[Последний]]

Строка итогов появится, как только введете какую-нибудь формулу под последней строкой таблицы. В Excel там автоматом появлялись функции SUBTOTAL для расчета по видимым строкам, но можно и обычные функции вроде SUM тоже.
Новость тут

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

Пост от нашего читателя Алексея Odysseus 

Сегодня у нас не обычный материал, а задачка.

Есть тарифы на электроэнергию: дневной и ночной. Задан период работы электроприбора. Максимальное время работы прибора - 24ч. Посчитать, сколько нужно заплатить за потребленную электроэнергию.

Сразу скажу, задача не на знание редких функция таблиц, а на логическое математическое мышление.

#Лирическое отступление. В чате часто спрашивают:
▸ есть ли какие-то хорошие курсы по таблицам?
▸ как научится писать такие формулы, как Вы?
▸ невероятно, а так можно было?
Из своего опыта, ответственно заявляю:
1. Курсы нужны чтоб быстро и грамотно и усвоить базовые принципы. Если Вы никогда не работали в таблицах, не делали курсовые с большим количеством расчетов, Вам обязательно нужно пройти "базовый курс".
2. Никто и никогда не становится специалистом за пару месяцев. Нужен опыт и практика. Наш чат предоставляет возможность получить и то, и другое!
3. Все сложные механизмы, в том числе и формулы, состоят из сочетания множества простых. Но чтобы уметь сочетать даже простые функции нужно уметь мыслить логически! И сегодня задачка именно на умение думать!

От авторов канала - Алексей, задачка сложная, помоги / подскажи в комментариях, в случае чего

И ответ на задачку от её автора, Алексея:

Приведу пример оптимального метода расчета для самого простого варианта.

Дано. 2 временных интервала, заданные началами и концами.
start1 - finish1 и start2 - finish2. Найти начало и конец пересечения интервалов, обозначим их как begin и end.

При условии: start1 < finish1 и start2 < finish2

begin = max (start1; start2)
  end = min (finish1; finish2)

На примерах с иллюстрации.

1. start1 = 6; finish1 = 18
    start2 = 9; finish2 = 15

begin = max (6; 9) = 9
end = min (18; 15) = 15

2. start1 = 6; finish1 = 18
    start2 = 3; finish2 = 15

begin = max (6; 3) = 6
end = min (18; 15) = 15

3. start1 = 6; finish1 = 18
    start2 = 9; finish2 = 21

begin = max (6; 9) = 9
end = min (18; 21) = 18

Комбинированная диаграмма: добавляем фон для периода

Хотим подсветить определенный период на диаграмме фоном.

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

2 Вставляем диаграмму и выбираем тип "Комбинированная".

3 Для второго ряда данных (фона) выбираем тип "с областями". Для самих данных — по вкусу (можно график или — как на скриншоте — столбики).

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

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

Сопоставляем список фамилий в Таблице и список файлов в папке, скрипт

Коллеги, Василий продолжает нас баловать решениями, он прислал очередной скрипт, слово автору.

У нас есть список документов в папке google диска, пусть это военные билеты или паспорта. 

Каждый документ лежит в папке диска и имеет название в формате `Фамилия Имя Отчество`.

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

Таблица с примером (делайте копию)

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

function ссылки_ссылки() {
  const idFolder = idFromUrl('https://drive.google.com/drive/u/0/folders/17uqPgg09Y3EaJJnkbutmzQfOZqIEMi0b'); // ID папки с файлами
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  const range = sh.getRange(1, 1, sh.getLastRow(), 1);
  let values = range.getValues();
  const files = DriveApp.getFolderById(idFolder).getFiles(); // забираем все файлы из папки

  var filesM = [];

  while (files.hasNext()) { // создаём массив [Имя, Url] каждого файла
    var file = files.next();
    filesM = [...filesM, [file.getName(), file.getUrl()]];
  };

  values = values.map((x, i) => {
    const fileUrl = i == 0 || !x[0] ? '' : filesM.filter(z => new RegExp(x[0]).test(z[0]))[0]?.[1] || 'не найдено';
    return [x[0], fileUrl];
  });

  range.offset(0, 0, values.length, values[0].length).setValues(values);
};

function idFromUrl(url) { // функция для извлечения ID из URL
  return url.match(/[-\w]{25,}/)[0];
};

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

Примеры применения ссылок на таблицы в формулах
начало про ссылки в "умных" таблицах тут

Пара примеров того, как можно использовать ссылки на таблицы на практике.

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

=VSTACK(ПерваяТаблица[#ALL]; [Вторая]; ... [ЕщеТаблица])

Если нам надо загрузить умную таблицу из другого файла — засунем ее во второй аргумент IMPORTRANGE. Если нужно с заголовками — опять-таки, добавляем #ALL:

=IMPORTRANGE("ID исходной таблицы"; "УмнаяТаблица[#ALL]")

Только в случае с IMPORTRANGE мы берем название таблицы в кавычки, т.к. это не активная ссылка, а текстовый аргумент этой функции.

И вот в обоих случаях нам не надо беспокоиться о том, сколько будет строк в таблицах-источниках. Все новые строки соберутся в одну пачку функцией VSTACK или будут импортированы IMPORTRANGE.

Запускаем скрипт ровно в 55 минут

Друзья, мне написал Владислав - у него есть скрипт и он хочет его запускать каждый час в конкретной время (например, в 55 минут каждого часа)

Мы решили помочь и написали скрипт, который

1) запускает нужный нам скрипт;
2) далее проверяет, есть ли скрипт в триггерах проекта, если есть - удаляет этот триггер
3) далее создаёт новый триггер, на следующий час, чтобы тот запустился в введенные в коде минуты (аргумент mins)

function googleSheets() {
  setDate(); // запускаем нашу функцию

  let fName = arguments.callee.name; // получаем имя текущей функции
  let triggers = ScriptApp.getProjectTriggers(); // удаляем триггер с текущей функцией, если он есть
  for (let i = 0; i < triggers.length; i++) {
    let trigger = triggers[i];
    if (trigger.getHandlerFunction() == fName) {
      ScriptApp.deleteTrigger(triggers[i]);
    };
  };

  let dt = new Date(); //рассчитываем время для запуска функции в следующий раз
  let h = dt.getHours();
  let mins = 44; //устанавливаем триггер на следующий час на это время
  let newDt = new Date(dt.setHours(h, mins, 0) + 60 * 60 * 1000);
  Logger.log(newDt);

  ScriptApp.newTrigger(fName) // ставим функцию на триггер
    .timeBased()
    .at(newDt)
    .create();
};

function setDate() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  sh.getRange((sh.getLastRow() || 0) + 1, 1)
    .setValue(new Date());
};

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

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

Проектная диаграмма с выделением выходных отдельным цветом

(или не выходных, а определенных дней недели — тут вы по аналогии справитесь😉)

Создаем два правила условного форматирования с формулами и выбираем разные цвета.

Для рабочих:

=AND(первая дата с закреплением строки>=начало первого этапа с закреплением столбца; первая дата<=конец первого этапа;WEEKDAY(дата;2)>5)

Для выходных (уже не в общем виде, а с ячейками):

=И(D$1>=$B2;D$1<=$C2;ДЕНЬНЕД(D$1;2)<6)

Таблица

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

Наш чат, в котором мы уже 6 лет отвечаем на ваши вопросы: @google_spreadsheets_chat

ПС Кто-то напишет в личку и предложит помочь - не отправляйте деньги, вас обманут

Оглавление нашего канала, где перечислены все наши посты: Оглавление канала

Полезные чатики по теме

Помимо нашего чата советуем чат наших друзей по скриптам: @googleappsscriptrc

И по Таблицам: @google_sheets_pro

👾 Делитесь в комментариях чатами по Таблицам, Excel, программированию, добавим в пост

Самое часто встречающееся текстовое значение

Самое частое число — это мода. Функция МОДА / MODE.

А как найти самое часто встречающееся текстовое значение?
Бен Коллинс в своей книге предлагает решение через MATCH (можно и XMATCH, которая просто по умолчанию ведет точный поиск без обязательного третьего аргумента = 0)

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

IFNA(XMATCH(B2:B;B2:B);)

Потом ищем самое частое число в этом диапазоне:

MODE(IFNA(XMATCH(B2:B;B2:B);))

И извлекаем по этому порядковому номеру текст ИНДЕКСом / INDEX:

=ArrayFormula(INDEX(B2:B; MODE(IFNA(XMATCH(B2:B;B2:B)))))

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


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

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

Редактируем Google Форму прямо из Таблицы!

Друзья, мы подготовили Таблицу, с помощью неё вы сможете облегчить себе редактирование Google Формы.

Как всё работает - на гифке, гифка сегодня со звуком.

Что происходит после запуска скрипта в Таблице:
1) в четвертой строке листа "🔥" Таблицы - вопросы, в пятой строке - тип каждого вопроса, под каждым вопросом и типом - варианты ответа, в ячейке "🔥!B2" - форма, которую будем редактировать
2) запускаем скрипт кликом на кнопку и код ищет каждый вопрос по названию в Google Форме
3) если вопрос найден - то скрипт обновляет варианты ответа
4) если вопрос не найден - скрипт добавляет этот вопрос в Google Форму с заданным типом и вариантами ответа

Типы вопросов, с которыми умеет работать скрипт:
CHECKBOX – выбираем ответ на вопрос чекбоксами, можно выбрать несколько вариантов
LIST – выбираем ответ из выпадающего списка
MULTIPLE_CHOICE – выбираем ответ в переключателе

Чтобы воспользоваться Таблицей - копируйте себе

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Друзья, с началом нового рабочего года вас!

Чтобы постепенно входить в рабочий режим, начнем с забавной формулы, вдохновленной поздравлением табличного гуру Бена Коллинса. Он там использовал похожую формулу, чтобы получить номер нового года текстом.

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

А мы это дело переведем на русский или другой нужный язык с помощью GOOGLETRANSLATE.

А потом останется убрать слово "бат" (бата, батов — смотря какое число будет). С помощью REGEXREPLACE.

Повторимся, для нормальной реализации (с рублями-копейками, для договоров) нужна пользовательская функция или надстройка с ней — про это мы тоже напишем

=REGEXREPLACE(GOOGLETRANSLATE(BAHTTEXT(A1);"th";"ru");"бата?\.|батов \.";"")

Диаграмма с выбором рядов (флажками)

Добавляем флажки для каждого ряда данных.

Дублируем данные для диаграммы, но формулой, которая будет возвращать значение из исходного диапазона только в том случае, если в соответствующем столбце флажок включен, то есть там значение TRUE / ИСТИНА. А если флажок выключен, то будет возвращать значение N/A с помощью одноименной функции. А такие значения на диаграммах не отображаются. Что нам и надо!

=IF(флажок;ячейка с данными;NA())

Строки с формулами можно скрыть, чтобы не любоваться всеми этими НД-шками, а только любоваться данными да диаграммой волшебной.

P.S. Так как периодически мы видим такое в чатах и таблицах, напомним: если вы проверяете статус флажка, не нужно отдельно прописывать равенство ячейки с флажком значению TRUE.
То есть формулы (где B$1 — ячейка с флажком)

=IF(B$1;...

и

=IF(B$1=TRUE;...

будут работать одинаково. Потому что включенный флажок — это TRUE. Так что в первом варианте будет TRUE. И во втором TRUE = TRUE вернет TRUE. Так что ничего страшного в избыточности нет, но и не нужно :)

P.P.S. В Excel все будет работать аналогично. Правда вот флажки в ячейках есть лишь в 365, а во всех старых версиях можно вставить флажки — элементы управления с вкладки ленты "Разработчик". Подробнее — в статье.

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

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

Как выделить / найти все формулы на листе?

Можно просто нажать Ctrl + `
Или в меню: Вид - Показать - Формулы (View - Show - Formulae), или Alt+V + S + A.

Тогда вы будете видеть формулы в самих ячейках. Там, где введены значения, останутся значения.

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


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