Google Таблицы, страница 11, все посты канала

Google Таблицы

А как в Excel? Ну если у вас Microsoft 365, то наслаждайтесь LAMBDA и вспомогательными функциями - все будет работать аналогично. А если версия до 2021? Всегда есть простор для формульно-массивного безумия! Вот один из вариантов - предлагайте в комментариях свои, как бы вы решили такую задачу😉

=СЧЁТЗ(СМЕЩ(F3;СТРОКА(3:11)-3;0;1;100))

В более общем виде:

=СЧЁТЗ(СМЕЩ(первая ячейка диапазона;СТРОКА(строки диапазона)-корректировка ;0;1;число столбцов в диапазоне))

Что тут происходит? Функция СМЕЩ / OFFSET выдает диапазон - шириной в сто столбцов (с запасом), высотой в одну строку, с началом в столбце F. Каждый раз смещаемся исходя из номера строки - на 0, 1, 2 и так далее строк вниз, получая тем самым ссылки на диапазоны, начинающиеся в F2, F3 и т.д. И с помощью СЧЁТЗ / COUNTA считаем количество значений. На всякий напомним - в старых формулах массива Excel нужно заранее выделить диапазон (и это, конечно, минус старых массивов - потому что в случае с LAMBDA и вообще динамическими массивами можно ввести формулу в одну ячейку), где будет результат, и нажать Ctrl+Shift+Enter для ввода формулы. Фигурные скобки, показывающие, что это формула массива (но не нового типа), появляются автоматически.

Google Таблицы

Задача: посчитать количество значений (или что-то еще, не столь важно - мы рассмотрим на примере счета) в каждой строке одной формулой. Здесь можно воспользоваться одной из новых функций, предназначенных для использования вместе с LAMBDA, а именно BYROW. Она позволяет применить вычисление к каждой строке массива. Синтаксис:

BYROW(массив данных ; LAMBDA(строка; вычисление (строка)))

Первый аргумент - весь диапазон, в котором надо обработать каждую строку. Второй - функция LAMBDA. В ней первый аргумент - переменная (называйте как хочется), обозначающая каждую строку в массиве (первом аргументе BYROW). Второй - то, что мы делаем с каждой строкой. То есть вводим формулу, в которой ссылаемся по тому же самому имени к строке. Если нужно считать количество значений в каждой строке, применяем СЧЁТЗ / COUNTA:

=BYROW(D2:Z;LAMBDA(массив;СЧЁТЗ(массив)))

P.S. Если нужно обрабатывать столбцы - то, соответственно, пользуем BYCOL. Таблица с примером Смотрите также: Накопительный итог с помощью функций SCAN и LAMBDA

Google Таблицы

Работа с отчётами, таблицами и графиками отнимает кучу времени? Нужно отчитаться за работу перед начальством, а выделить самое важное и наглядно показать результат не получается? А что, если от этого будет зависеть твоя карьера? Приходи на бесплатный онлайн-практикум по отчётам и таблицам от академии презентаций Bonnie&Slide и научись создавать понятные и эффективные отчёты за счет правильной компоновки данных и шаблонов, чтобы получить повышение на работе: - Без суеты и горящих сроков - Без глубокой проработки дизайнерских навыков - Следуя простым, понятным инструкциям Все участники практикума получат возможность выиграть крутые подарки. Регистрация #реклама

Google Таблицы

onEdit скрипт, который предлагает вернуть старое значение Друзья, привет! Очередная задачка наших клиентов - есть цветные строки, редактирование которых производить нежелательно. Мы не стали закрывать строки физически (создавая защищенные диапазоны), а просто написали onEdit скрипт, проверяющий заливку ячейки, которую пользователь редактирует и если фоновый цвет не белый - скрипт выводит диалоговое окно, в котором предлагает вернуть предыдущее значение, либо оставить введенное. Сам код

function onEdit(e) {
  var range = e.range
  //проверяем фон ячейки, которая редактируется
  if (range.getBackground() != '#ffffff') {
    var old_value = e.oldValue;
    var ui = SpreadsheetApp.getUi();
    //выводим диалоговое окно
    var response = ui.alert(
      Сохранить изменения - OK\n\nВернуть старое значение [${old_value}] - CANCEL,
      ui.ButtonSet.OK_CANCEL);

    //обрабатываем результат выбора пользователем, CANCEL - возвращаем старое значение, ОК - ничего не делаем
    response == ui.Button.CANCEL ? range.setValue(old_value) : '';
  }
}

Таблица с кодом и примером PS Недавно в нашем чате был вопрос о том, как переносить строки по чекбоксу с помощью onEdit, вот пост и про это

Google Таблицы

Символы подстановки в функциях Google Таблиц Друзья, хотим напомнить про символы подстановки (wildcard), тем более что в новых функциях они работают несколько иначе. Итак, символов подстановки есть три: * (звездочка) - любое количество любых символов, в том числе нулевое, то есть на месте звездочки может не быть ничего. ? (знак вопроса) - один любой символ. В отличие от звездочки, на месте знака вопроса точно должен быть символ: пробел, цифра, буква, символ ~ (тильда) - используется ,чтобы искать именно звездочку (~*), знак вопроса (~?) или тильду (~~). Символы подстановки работают по умолчанию в следующих функциях: - ВПР / VLOOKUP и ПОИСКПОЗ / MATCH - СУММЕСЛИ (МН) / SUMIF(S), СЧЁТЕСЛИ(МН) / COUNTIF(S), СРЗНАЧЕСЛИ(МН) / AVERAGEIF(S) - Функциях баз данных. БДСУММ / DSUM, ДСРЗНАЧ / DAVERAGE, БСЧЁТ / DCOUNT, БСЧЁТА / DCOUNTA и других - ПОИСК / SEARCH В новых функциях ПОИСКПОЗX / XMATCH и ПРОСМОТРX / XLOOKUP символы подстановки по умолчанию не работают! Но у них есть аргумент "режим_сопоставления" (match_mode), в котором есть следующие варианты: 0 - точный поиск (по умолчанию). Символы подстановки не работают. 1 ближайшее большее значение (или точное совпадение) -1 ближайшее меньшее значение (или точное совпадение) 2 - точный поиск с символами подстановки. То есть чтобы все работало как в ВПР с последним аргументом, равным нулю, нужен режим 2. Иначе магия звездочек и знаков вопросов не будет работать. P.S. В Excel символы подстановки работают еще и в окне "Найти и заменить" и в условиях расширенного фильтра. Всякое по теме: Примеры условий с символами подстановки в функциях СУММЕСЛИ / SUMIF, СЧЁТЕСЛИ / COUNTIF, СРЗНАЧЕСЛИ / AVERAGEIF СУММЕСЛИМН / SUMIFS с флажком (включаем и выключаем условие) Функции баз данных ВПР со звездочкой

Google Таблицы

Накопительный итог в новых реалиях Господа, после того, как в таблицах появились функции LAMBDA, SCAN, MAP, BYROW, BYCOL, REDUCE, MAKEARRAY считать накопительный итог по каждой строке стало гораздо проще. Рассказываем. Функция SCAN умеет возвращать промежуточные значения (читайте - по каждой строке), путем применения вложенной функции LAMBDA. Синтаксис такой:

=SCAN(начальное значение [аккумулятор]; диапазон; LAMBDA)

Формула для накопительного итога по каждой строке:

=SCAN(0; A1:A5; LAMBDA(acc;x; acc+x))

Значит, 1) задаём аккумулятор и диапазон значений 0; A1:A5 2) обращаемся к ним в лямбде, как к acc и x, добавляя каждое значение, построчно, к аккумулятору, acc + x 3) SCAN выводит аккумулятор, он же - накопительный итог, по каждой строке в Таблицу Делитесь в комментариях своими способами применения новых формул, а также приходите к нам в чат

Google Таблицы

Самое время начать карьеру в IT-сфере! Участвуйте в бесплатном интенсиве с 15 по 17 сентября в 19:00 по московскому времени. Обучитесь новой профессии — 1С-разработчик. Регистрируйтесь: 👉 https://clc.to/6oshxg. 👨‍💻 Познакомитесь с платформой 1С и её основными объектами. Создадите базу для торгового предприятия и на её примере поймёте, что 1С — это просто. 💼 К концу интенсива подготовите проект, который станет первым кейсом в вашем портфолио разработчика. 🎁 Всем участникам подарим чек-лист по созданию баз данных в 1С и электронную книгу Пола Доэрти и Джеймса Уилсона «Человек + машина» издательства МИФ. Участвуйте, задавайте вопросы и получите сертификат на 10 000 рублей на любой курс Skillbox. #реклама

Google Таблицы

Перемещение после ввода данных в ячейку Друзья, думаем, многие из вас знают, что после ввода данных можно нажимать не только Enter. Можно завершить ввод и попасть в любую соседнюю ячейку: Enter - вниз Shift-Enter - вверх Tab - вправо Shift-Tab - влево Если вы заполнили несколько ячеек в одной строке через Tab и потом нажмете Enter, то переместитесь в начало следующей строки (под первый заголовок). Ну а еще в Таблицах можно заранее выделить диапазон или строку, куда вы будете вводить данные. И тогда при нажатии Enter вы будете перемещаться к следующей ячейке в пределах этого диапазона! Если выделена только строка (например, заголовки), то к следующей ячейке в столбце справа. Если диапазон - то сначала в пределах первого выделенного столбца, а потом к первой ячейке во втором выделенном столбце. Ну а в Excel еще есть Ctrl+Enter - после ввода остаемся в той же ячейке. А если выделено несколько ячеек, то ввод будет осуществляться в каждую из них! Даже если это несмежные диапазоны/ячейки. И в Excel можно изменить то направление, куда мы перемещаемся после нажатия Enter - в параметрах (в разделе "Дополнительно" / Advanced).

Google Таблицы

Как зарабатывать на Excel и Google Таблиц от 70 000 ₽ в месяц: 3 способа. 1. Найти работу, где требуется Excel. Вам необязательно быть бухгалтером или экономистом. Знание электронных таблиц нужно аналитикам, специалистам по обработке данных, маркетологам, предпринимателям, бизнес-консультантам. 2. Обучение и консультирование. Если стать консультантом по Excel, то можно как обучать клиентов работе с таблицами, так и создавать для них готовые шаблоны под заказ. 3. Зарегистрироваться на сайтах фриланса и брать заказы на Excel-проекты. Средний бюджет задач по Excel — 5000 рублей. Профи легко решают подобные задачи за 10 — 15 минут. Освоить Excel и Google Таблицы можно на онлайн-курсе «Excel + Google Таблицы с нуля до PRO» от Skillbox. Всего за 4 месяца вы научитесь автоматизировать свою работу — тем самым освободите время для других задач. Прямо сейчас у нас действует скидка на обучение. А также Skillbox предлагает рассрочку всем пользователям платформы. Узнать подробнее: https://clc.to/xUMIYA #реклама

Google Таблицы

Чего нам не хватало в Таблицах Ну, например, функций ПРОСМОТРX / XLOOKUP, ПОИСКПОЗX / XMATCH и LAMBDA. И вот они здесь (точнее, на подходе - ждем, когда все они будут доступны у всех). Не менее важной новостью является появление пользовательских функций "без кода", то есть можно будет один раз написать сложную формулу и дать ей имя, а потом пользоваться ей, в том числе импортировать в другие таблицы. Вот новость от Google. Пользовательские функции, конечно, были, но это были скрипты, а не формулы, и ими можно было пользоваться только в той таблице, в которой был написан код. Увы, эта опция будет недоступна для пользователей с бесплатными аккаунтами 😐 Про функцию LAMBDA мы писали здесь (она и в Excel появилась недавно). Синтаксис в Google Таблицах будет отличаться от Excel! Новые пользовательские функции (Named functions) - по сути и есть LAMBDA, с возможностью задать имя и делать это все в специальном интерфейсе (это будет боковая панель, вызываемая в меню Data / Данные - пока можно посмотреть на гифку в новости от Google). Про ПРОСМОТРX здесь. ПОИСКПОЗX / XMATCH - это тот же MATCH, но в новой функции по умолчанию ведется точный поиск, а еще можно искать снизу вверх, а не сверху вниз. В ближайшее время расскажем подробнее про новинки. Обновили табличку со сравнением двух редакторов: Схватка двух ёкодзун. Сравнение Google Таблиц и Excel

Google Таблицы

Чего нам не хватало в Excel Ну, например, функции IMAGE. В русскоязычном Excel она будет называться ИЗОБРАЖЕНИЕ. Синтаксис очень похож на гугло-табличный. Добавляется альтернативный текст (второй аргумент), нумерация режимов отображения с нуля, а не с единицы. Также обязательным является один аргумент - ссылка на изображение. Попробовали закинуть Excel с этой функцией в Таблицы - все работает. В обратную сторону - в формуле будет @IMAGE (которая будет вызывать ошибку ИМЯ / NAME - дескать, не знает Excel такого), то есть если у вас Excel на русском, то придется вызвать окно "Найти и заменить" и поменять на русскоязычное название. В Excel на английском - убрать собачку из формул. Разумеется, все это при наличии актуальных обновлений и Microsoft 365.

Google Таблицы

TOO MUCH IMPORTRANGE Друзья, представьте ситуацию – у вас 100 Таблиц, в них точно есть функции IMPORTRANGE и вы хотите узнать, какие Таблицы указаны внутри этих функций. То есть, откуда тянутся данные. Мы подготовили скрипт, который решит эту проблему (скрипт и короткое описание: pastebin.com/EcRZwhg0). А зачем это может понадобиться? Расскажу про проблему наших клиентов – одна Таблица была использована внутри IMPORTRANGE слишком много раз (>500) и после очередного раза всё перестало работать, данные из Таблицы перестали передаваться, пользователи видели ошибку как на картинке, а еще в эту несчастную Таблицу стало нельзя добавлять новых пользователей с правами чтение или редактирование. Наше решение: 1) Мы выяснили, какие Таблицы у нас лежат внутри рабочей папки нашим скриптом Drive Columbus; 2) Прошлись по этому списку Таблиц скриптом и узнали, какие Таблицы указаны внутри IMPORTRANGE в этих Таблицах; 3) Сделали копию Таблицы-донора и написали скрипт, который вставил формулу IMPORTRANGE с этой Таблицей в ряд Таблиц по списку; 4) Использовали скрипт от Михаила Смирнова, который прошелся по списку Таблиц с обновленной формулой IMPORTRANGE и раскрыл доступ к Таблицам автоматически, кликать на "расшарить доступ" не пришлось; В итоге достаточно неприятная проблема была решена и сейчас сотни Таблиц по-прежнему работают.

Google Таблицы

Как научиться работать с базами данных? Запишитесь на бесплатный интенсив Skillbox, который пройдёт с 1 по 3 сентября в 21:00 по московскому времени. Всего за 3 вечера вы узнаете, как хранятся данные в сети, познакомитесь с языком SQL и напишете набор запросов к базе данных. Подробности: 👉 https://clc.to/D5TpvQ. Что будет: ● 3 дня вебинаров по 1,5 часа для полного погружения в тему; ● реальные задачи, которые помогут закрепить знания и понять, в каком направлении двигаться дальше; ● общение с другими участниками и ведущим интенсива в закрытом чате в Telegram; ● самостоятельное создание проекта, который высоко оценят потенциальные работодатели. Спикер интенсива — специалист по анализу данных, сертифицированный SQL-разработчик от Microsoft Мкртич Пудеян. 🎁 Всем зарегистрировавшимся подарим чек-лист для проверки базовых навыков по SQL, которые требуют на собеседованиях. Подключайтесь к прямым эфирам и используйте возможность получить сертификат на 10 000 рублей на любой курс Skillbox. А в конце интенсива подарим участникам электронную книгу издательства МИФ. #реклама

Google Таблицы

Нарастающий итог: закрепляем только первую ячейку в диапазоне Если вы хотите считать в отдельном столбце нарастающий итог, никто не помешает вам закрепить только начало диапазона, но не его конец. 1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить") 2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.

=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке. Альтернативное решение, одна формула массива: =ARRAYFORMULA(SUMIF(row(B1:B16),"<="&row(B1:B16),B1:B16)) Про такую формулу мы писали вот здесь

Google Таблицы

Информация - это ценность? Да, если уметь ее правильно готовить. В любой компании собирается и генерируется много информации... Но, по данным Gemalto, 65% компаний не могут самостоятельно проанализировать данные. А значит, извлечь пользу, прогнозировать, оптимизировать процессы и принимать решения, основанные на данных. Поэтому те, кто научится с ними работать, будут востребованы и спустя годы в любой индустрии - от финансового сектора до геймдева. При этом спрос на таких специалистов уже сильно превышает предложение, а зарплатная «вилка» middle-аналитиков стартует от 130 000 рублей. Получить необходимые знания и практический опыт можно с нуля — на примере 32 бизнес-кейсов на курсе «Аналитик данных» от Академии Eduson. Интерактивные тренажёры и методика от простого к сложному не дадут запутаться: начнете с Excel, а потом овладеете SQL, Python и Power BI. Преподаватели из «Яндекса», Datalatte, Softline и Ghetto Studio делятся опытом с первых уроков. Личный куратор будет на связи 365 дней со старта обучения, а сама Академия поможет с трудоустройством и отправит ваше резюме своим компаниям-партнерам, среди которых Avito, ВТБ и Mercedes Benz. Чтобы начать чувствовать себя уверенно в профессии и найти востребованную работу в IT — оставляйте заявку по ссылке. А промокод ТАБЛИЦЫ зафиксирует за вами скидку -55%. Карьерная консультация в подарок. #реклама

Google Таблицы

Кликаем – строки выделяются Друзья, onSelectionChange такой же простой триггер, как onEdit, но запускается просто при выделении ячеек. Скрипт из ГИФки проверяет, какой диапазон был сохранён в ScriptProperties в предыдущий раз, убирает с этих строк заливку, далее закрашивает строки, которые были выделены сейчас и сохраняет этот диапазон в ScriptProperties (чтобы при следующем запуске убрать заливку с него). Сразу предупреждаем, что работать всё будет медленно, так как скрипту приходится реагировать на каждый клик по ячейкам. 🤓 Если найдёте триггеру применение в своем Табличном хозяйстве – напишите нам в комментариях.

const pr_key = 'lr';
const colour = '#800080'; //en.wikipedia.org/wiki/Web_colors

function onSelectionChange(e) {
  const range = e.range;
  const sh = e.source.getActiveSheet();
  const range_a1 = ${range.rowStart}:${range.rowEnd};
  const last_range_a1 = ScriptProperties.getProperty('lr');
  if (last_range_a1) {
    sh.getRange(last_range_a1)
    .setBackground(null);
  };
  sh.getRange(range_a1)
    .setBackground(colour);
  ScriptProperties.setProperty('lr', range_a1);
};

Оглавление нашего канала: тыц Наш чат: тыц-тыц

Google Таблицы

Работа с отчётами, таблицами и графиками отнимает кучу времени? Нужно отчитаться за работу перед начальством, а выделить самое важное и наглядно показать результат не получается? А что, если от этого будет зависеть твоя карьера? Приходи на бесплатный онлайн-практикум по отчётам и таблицам от академии презентаций Bonnie&Slide и научись создавать понятные и эффективные отчёты за счет правильной компоновки данных и шаблонов, чтобы получить повышение на работе: - Без суеты и горящих сроков - Без глубокой проработки дизайнерских навыков - Следуя простым, понятным инструкциям Все участники практикума получат возможность выиграть крутые подарки. Регистрация #реклама

Google Таблицы

Видеоурок для новичков: текстовые функции Друзья, привет! Делимся с вами уроком из курса "Драйв. Гугл Драйв" - про работу с текстовыми значениями: окно "Найти и заменить", функция TRIM, изменение регистра, разделение текста (командой "Разделить текст на столбцы" и функцией SPLIT), объединение текстовых значений, извлечение фрагментов из текста. https://www.youtube.com/watch?v=Fdkdm3h7Ylc В курсе таких видео 90 - про диск, таблицы (конечно, ко всем урокам прилагаются исходные и заполненные таблицы), документы, календарь, почту, презентации (от ведущего дизайнера МИФа Ольги Королёвой). Слушатели уже поставили 249 оценок урокам курса. 244 из них (98%) - пятерки 5️⃣ Коллеги из МИФа сделали промокод на скидку для вас - 40%DGD. Действует до 29 августа (12-00). www.mann-ivanov-ferber.ru/courses…s/gdrive

7.7. Функции для работы с текстом.

+НАЙТИ МИФ: Наши курсы: https://mif.to/vsekursy Наши книги: https://mif.to/vseknigi ВКонтакте: https://vk.com/mifbooks Telegram: https://t.me/mifbooks

YouTube

Google Таблицы. Страница 11

Google Таблицы

Google Таблицы

Google Таблицы

Реклама

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Google Таблицы

Реклама

Google Таблицы

Google Таблицы

Google Таблицы