Ошибка массив не выведен

Функция FILTER Таблиц Гугл отображает только те строки или столбцы в диапазоне, которые соответствуют заданным условиям.
Отличительной особенностью этой формулы является то, что она работает как со строками, так и с столбцами.

Примечание Это большой и подробный указатель на возможности формулы FILTER на русском языке. Все примеры доступны в Таблице, см. раздел Ссылки
.

Описание

Функция FILTER универсальная и очень гибкая. Она очень быстрая и легко читается. Используя такие функции как REGEXMATCH и MATCH, можно имитировать работу SQL-подобных запросов типа like и выбора на основе списка. Это действительно удобно, когда требуется отобрать большое количество данных, не зная конечного числа аргументов в уловии отбора.

Для всех примеров будет использоваться следующий набор данных

Примеры

Колонка соответсвует конкретному значению

=FILTER('Данные'!A2:F18;'Данные'!F2:F18="A")

Данные содержат заданную подстроку

Вывести все имена, в которых есть буквосочетание ль

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "ль"))

Данные начинаются с определенной строки

Вывести имена, которые начинаются на А

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "^А"))

Данные не начинаются с определенной строки

Вывести имена не начинающиеся на А

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "^[^А]"))

Сравнение чисел

Вывести данные, числа которых меньше 2500

=FILTER('Данные'!A2:F;'Данные'!D2:D<2500)

Числа содержат определенную цифру

Номер содержит цифру 2

=FILTER('Данные'!A2:F;REGEXMATCH(TEXT('Данные'!A2:A; "0"); "2"))

Числа оканчиваются на определенную цифру

Номер оканчивается на цифру 2

=FILTER('Данные'!A2:F;REGEXMATCH(TEXT('Данные'!A2:A; "0"); "2$"))

Сравнение дат

Выбрать всё до даты 31.12.2017

=FILTER('Данные'!A2:F;'Данные'!C2:C<=DATE(2017;12;31))

Даты только определенного года

Только 2017 год

=FILTER('Данные'!A2:F;REGEXMATCH(TEXT('Данные'!C2:C; "yyyyMMdd"); "2017"))

Даты только определенного месяца

Только август 2019

=FILTER('Данные'!A2:F;EOMONTH('Данные'!C2:C;1)=EOMONTH(DATE(2019;8;1);1))

Любой август

=FILTER('Данные'!A2:F;MONTH('Данные'!C2:C)=8)

Отбор по счету (по очереди)

Выбрать каждую шестую строку

=FILTER('Данные'!A2:F; NOT(MOD(ROW('Данные'!A2:A)-1; 6)))

Выбрать каждую нечетную строку

=FILTER('Данные'!A2:F; ISODD(ROW('Данные'!A2:A)-1))

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

Выбрать данные, имена которых находятся в списке {"Георгий";"Ермак";"Юлиус"}

=FILTER('Данные'!A2:F;IFERROR(MATCH('Данные'!B2:B;{"Георгий";"Ермак";"Юлиус"};0);))

Данные, которые соответствуют списку

Выбрать имена, которые находятся в списке. Список в I8:I12

=FILTER('Данные'!A2:F;MATCH('Данные'!B2:B;I8:I12;0))

Данные, которые отсутствуют в списке

Выбрать имена, которых нет в списке. Список в I15:I19

=FILTER('Данные'!A2:F;ISNA(MATCH('Данные'!B2:B;I15:I19;0)))

Данные, которые соответствуют “нечеткому” списку значений

Выбрать все, в поле Строка которых, есть последовательность символов “x” и, любая цифра или тире “-”

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!E2:E;"x[d-]"))

Вывести все данные, где поле Имя начинается с буквы из списка

Список находится в I9:I12

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "(?i)^(" & TEXTJOIN("|";1; I9:I12) & ")"))

Пример выбора женских имен

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "(?i)(" & TEXTJOIN("|";1; I15:I19) & ")"))

или

=FILTER('Данные'!A2:F;REGEXMATCH('Данные'!B2:B; "(?i)(" & JOIN("|"; {"а$";"я$"}) & ")"))

Пример выбора мужских имен

=FILTER('Данные'!A2:F;NOT(REGEXMATCH('Данные'!B2:B; "(?i)(" & TEXTJOIN("|";1;I23:I27) & ")")))

Выборка с условием ИЛИ

Требуется отфильтровать данные в Дате которых указан 2019 год или Группа которых равна "B"

=FILTER('Данные'!A2:F;(YEAR('Данные'!C2:C)=2019) + ('Данные'!F2:F="B"))

Бесконечные диапазоны

Бесконечные диапазоны в FILTER могут создавать некоторые неудобства, особенно, если в выборку попадает достаточно много пустых строк. Они просто занимают место в результирующей Таблице. Если на это место внести данные, то получим ошибку

Ошибка
Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне.

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

В примере ниже на листе Данные 1000 строк, из которых только 20 заполнены значениями. Вот результаты фильтров

Сравните

=FILTER('Данные'!A2:F;YEAR('Данные'!C2:C)<2018;'Данные'!A2:A<>"")

=FILTER('Данные'!A2:F;YEAR('Данные'!C2:C)<2018)

В первой формуле учитываются и не выводятся пустые строки 'Данные'!A2:A<>""

Фильтр по колонкам

Фильтр по колонкам проверяет условие в горизонтальном массиве. Если значение условно истинное, то колонка выведется, иначе нет.

Жестко заданные колонки по номерам. Только вторая и шестая колонка

=FILTER('Данные'!A1:F;{011})

Колонки по определенному условию. Если имя колонки равно “Имя” или “Группа”

=FILTER('Данные'!A1:F;ARRAYFORMULA(REGEXMATCH('Данные'!A1:F1;"(?i)(имя|группа)")))

Динамический фильтр колонок

В диапазоне F2:F7 находятся условия для фильтра

=FILTER('Данные'!A1:F;TRANSPOSE(F2:F7))

Ссылки

• FILTER — Cправка — Редакторы Документов
• Примеры формул в Таблице

Google Docs Editors Help

Sign in

Google Help

• Help Center
• Community
• Google Docs Editors
• Privacy Policy
• Terms of Service
• Submit feedback

Send feedback on…

This help content & information

General Help Center experience

• Help Center
• Community

Google Docs Editors

Как сделать массив с перезаписью

Kashimirush Дата: Пятница, 28.06.2019, 08:17 | Сообщение № 1 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Суть такая есть 2 таблицы со связанными данными между собой, Ввод связанных данных производится массивом (В основном {«Название колонки»;ArrayFormula(VLOOKUP())}). Данные в таблице 2 никак не влияют на таблицу 1, а Данные таблицы 1 выводятся в таблицу 2 в зависимости от введенных данных в один из столбцов. Как пример если в столбце А2 поставить 1, в столбце В2 будет номер 34528, а если в А2 поставить 2 в В2 значение поменяется на 6985, и т.д. в зависимости от заполненности таблицы 2. Проблема в том что иногда приходится тасовать строки местами, при этом формула массива считает что в столбец В ввели данные, и массив выдает REF (Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне B2.) Как построить функцию массива так, чтобы она перезаписывала данные в нужном ей диапазоне, даже если кто то вручную введет там данные (они не нужны и вводятся по ошибке) Пример таблицы: Формула вбита в В1, если перетасовать Строки она слетает, но если удалить значения в колонке В все работает опять Работа, работа, перейди на Федота… Сообщение отредактировал Kashimirush — Пятница, 28.06.2019, 08:22   Ответить

Gustav Дата: Среда, 03.07.2019, 14:32 | Сообщение № 2 Группа: Друзья Ранг: Старожил Сообщений: 2449 Репутация: 995 ± Замечаний: 0% ± начинал с Excel 4.0, видел 2.1 А что Вы подразумеваете под тасовкой? Если отсортировать полные строки хоть по колонке A, хоть по колонке B, то все сортируется нормально. Если цеплять мышкой какую-нибудь ячейку в колонке A и перетаскивать ее в новое место в колонке A (на другую строку), то соответствующее значение в колонке B «следует» за своим аргументом из колонки A. Делать подобные перетаскивания в колонке B не получится — по понятным причинам. Если Вам надо придать какую-то последовательность значениям в колонке B, то можно скопировать значения из колонок A:B, скажем, в колонки D:E и там цеплять мышкой уже не одну ячейку, а две соседние в колонках D:E. Допустим, выделяете диапазон D2:E2 и перетаскиваете его в D9:E9 и т.д. с другими строками. Когда закончите, копируете колонку D в колонку A, а колонки D:E очищаете. МОИ: Ник, Tip box: 41001663842605   Ответить

Kashimirush Дата: Среда, 03.07.2019, 15:09 | Сообщение № 3 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 А что Вы подразумеваете под тасовкой? В гугл докс можно цеплять всю строку и перенести в то положение которое нужно. Таблица имеет большое количество данных, ваш вариант мне не подходит. Я ищу решение именно чтобы массив сам мог перезаписывать данные. На текущий момент создал кнопку со скриптом, который удаляет все ошибочно введенные данные в область действия массива. Но данное решение не удовлетворяет вопросам актуальности, если вовремя ошибку не увидел оператор и не нажал кнопку массив REFается и данные исчезают Работа, работа, перейди на Федота…   Ответить

Gustav Дата: Среда, 03.07.2019, 15:59 | Сообщение № 4 Группа: Друзья Ранг: Старожил Сообщений: 2449 Репутация: 995 ± Замечаний: 0% ± начинал с Excel 4.0, видел 2.1 Ну, если целыми строчками двигаете, тогда можно не использовать одну формулу массива в B1, а «по старинке» иметь отдельную формулу для каждой строки, начиная с B2 и далее вниз: [vba] Код =IFERROR(VLOOKUP(A2;’Лист2′!A:B;2;FALSE);»») [/vba] И таскайте полные строчки себе на здоровье! Формулы массива — вешь, несомненно, удобная и в ряде случаев даже очень полезная, но на них свет клином не сошелся. МОИ: Ник, Tip box: 41001663842605   Ответить

Kashimirush Дата: Четверг, 04.07.2019, 14:19 | Сообщение № 5 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 начиная с B2 и далее вниз: Так изначально и было, но таблицей пользуются порядка 15 человек Строки вниз доходят до 1000-2000 и далее, добавляются постепенно по 50-100 новых строк, и непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101, как раз поэтому и перешли на формулу массива. Скорее всего надо будет в дальнейшем переходить на создание базы данных, но это уже другая история. Проект создавали в спешке и дабы не тратить время на программистов начали работать в гугл докс. Работа, работа, перейди на Федота…   Ответить

Gustav Дата: Четверг, 04.07.2019, 23:09 | Сообщение № 6 Группа: Друзья Ранг: Старожил Сообщений: 2449 Репутация: 995 ± Замечаний: 0% ± начинал с Excel 4.0, видел 2.1 непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101 Ну, почему непонятным, очень даже понятным: случайно (думаю, все же не злонамеренно) перетаскивают ячейку в колонке A в другую строку — вот и начало «разъезда» формул по разным строкам. Ну, хорошооо… А попробуйте-ка «повалить» аналогичным образом вот такую конструкцию для одной строки (взамен предыдущей моей формулы), тоже начиная с B2 и вниз: [vba] Код =IFERROR(VLOOKUP(INDIRECT(«A» & ROW());’Лист2′!A:B;2;FALSE);»») [/vba] Обязательно сообщите, удастся ли? И если да, то как именно? P.S. Предвосхищая попытку «сломать» формулу путем вставки нового столбца перед имеющимся A, предложу еще такой самонастраивающийся вариант: [vba] Код =IFERROR(VLOOKUP(INDEX(A:A; ROW());’Лист2′!A:B;2;FALSE);»») [/vba] МОИ: Ник, Tip box: 41001663842605 Сообщение отредактировал Gustav — Четверг, 04.07.2019, 23:26   Ответить

Kashimirush Дата: Пятница, 05.07.2019, 08:09 | Сообщение № 7 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Gustav, Какая логика работы такого сочетания формулы? Минус формул еще в том что их постоянно нужно протягивать , т.к. мы изначально не знаем сколько строк будет в конечной таблице, операторы про это забывают и мне постоянно приходилось следить за таблицами и допротягивтаь формулы если операторы добавляли новые строки. Работа, работа, перейди на Федота…   Ответить

Kashimirush Дата: Пятница, 05.07.2019, 08:14 | Сообщение № 8 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Gustav, Все таки формулы тоже не идеально решение, да теперь данные возвращаются верно всегда, но при добавлении или нечаянном перемещении ячейки, формула не прописывается в пустую ячейку и я буду опять иметь гемор по поиску ошибок и по допротягиванию формул. Работа, работа, перейди на Федота… Сообщение отредактировал Kashimirush — Пятница, 05.07.2019, 08:24   Ответить

Kashimirush Дата: Пятница, 05.07.2019, 08:20 | Сообщение № 9 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Могу я в свой скрипт добавить как то время повторения допустим раз в час Работа, работа, перейди на Федота…   Ответить

Kashimirush Дата: Пятница, 05.07.2019, 10:00 | Сообщение № 10 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 А еще круче былоб если скрипт начинал действовать, если формула в одной из ячейке выдавала бы REF Работа, работа, перейди на Федота…   Ответить

Gustav Дата: Пятница, 05.07.2019, 18:37 | Сообщение № 11 Группа: Друзья Ранг: Старожил Сообщений: 2449 Репутация: 995 ± Замечаний: 0% ± начинал с Excel 4.0, видел 2.1 Созрел вариант со скриптом к первоначальной формуле массива. Пишем такой маленький скриптик: [vba] Код function onChange(e) {   var sheet = SpreadsheetApp.getActive().getActiveSheet();   if (sheet.getName() != ‘Лист1’) return;   if (e.changeType == ‘INSERT_ROW’) {     sheet.getRange(«B2:B»).clearContent();   }   } [/vba] Дальше создаем триггер (если раньше не делали — погуглите) с такими параметрами: [vba] Код Выберите функцию: onChange Выберите источник мероприятия: Из таблицы Выберите тип события: При изменении [/vba] Всё. Двигаем полные строчки в таблице на Листе 1. P.S. Могу я в свой скрипт добавить как то время повторения допустим раз в час Вот это как раз в сторону триггеров надо смотреть. МОИ: Ник, Tip box: 41001663842605 Сообщение отредактировал Gustav — Пятница, 05.07.2019, 18:44   Ответить

Kashimirush Дата: Понедельник, 08.07.2019, 07:38 | Сообщение № 12 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Gustav, TypeError: Не удается прочитать свойство «changeType» объекта undefined. (строка 4, файл T2) Ругается на 4 строку: if (e.changeType == ‘INSERT_ROW’) { Работа, работа, перейди на Федота…   Ответить

Kashimirush Дата: Понедельник, 08.07.2019, 07:51 | Сообщение № 13 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Gustav, Я написал в выходные такой скрипт Но он не инициируется автоматически нужно его запускать, где про триггеры почитать лучше? Работа, работа, перейди на Федота…   Ответить

Kashimirush Дата: Понедельник, 08.07.2019, 08:23 | Сообщение № 14 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Вроде разобрался, оставил свой скрипт , в редакторе скриптов нажал кнопочку триггеры текущего проекта — выбросило на новую страницу, в правом нижнем углу нажал плюсик добавить триггер. Там в всплывающем окне выбрал «свою функцию»; «Основное развертывание»; «Из таблицы»; «При редактировании» Теперь при редактировании скрипт срабатывается если формула в В1 REF-ается и удаляет все не нужное в диапазоне В2:В4000 (почему то при указании диапазона В2:В — скрипт выдает ошибку, мне 4000 вполне хватит) Результат можете смотреть в шапке поста , там ссылка на тестовую таблицу. Работа, работа, перейди на Федота… Сообщение отредактировал Kashimirush — Понедельник, 08.07.2019, 08:25   Ответить

Kashimirush Дата: Вторник, 06.08.2019, 12:13 | Сообщение № 15 Группа: Проверенные Ранг: Форумчанин Сообщений: 240 Репутация: 40 ± Замечаний: 0% ± Excel 2010 Теперь Google поправили этот косяк в самих таблицах, теперь при использовании формулы массива можно тасовать строки, массив не REF-ается. Работа, работа, перейди на Федота…   Ответить

Добрый день. Вопрос следующий. касается Гугол-таблиц. Есть список URL. По формуле с этого списка — парсятся заголовки страниц. Если он один (заголовок H3) — он — просто, как и нужно — прописывается напротив в соседнем столбце. Но если их несколько, выбивает ошибку !ССЫЛ (Массив не выведен, поскольку это привело бы к перезаписи в следующей строке; что понятно и логично). Проблема в том, что нужно — предусматривать место для заголовков H3 (нужно спарсить H2 — H3), т.к. их может быть 2 или 5, к примеру. И чтобы, когда я растягиваю формулу, относительно урл — появлялись и заголовки, то есть формировались дополнительные строки. Как это сделать? Фото вложил Заранее спасибо

В общем, чтобы в конечном итогу — было так — https://drive.google.com/open?id=188mkNOgCCp2MOXGoqoTmKII50czVbFj7

ternovsky Пользователь Сообщений: 302 Регистрация: 01.01.1970 Ternov

Добрый день, эксперты. Не могу понять, как это сделать: надо в каждую ячейку столбца С — проверить на наличие слов «список промокодов» — подкрасил желтым и если в тексте есть эти слова, то вернуть это слово в соседний столбец. Для примера как должно получится — столбец В. То есть выделить из текста «промокод» и вернуть его ячейку соседнего столбца.   Прошу помочь. Спасибо. Прикрепленные файлы тест.xlsx (9.41 КБ)

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	Формула массива, ввод тремя клавишами: =ИНДЕКС($E$2:$E$6;МАКС(ЕСЛИ(ЕЧИСЛО(ПОИСК($E$2:$E$6;C2));СТРОКА($E$2:$E$6)-1))) Чтобы не усложнять формулу, используется Е2. Запишите туда что-нибудь («нет», =»») или оставьте пустой — это значение будет отображаться, когда совпадений не найдено

Kuzmich Пользователь Сообщений: 8000 Регистрация: 21.12.2012	ternovsky, А слово из списка промокодов встречается в ячейке столбца С только один раз?

ternovsky Пользователь Сообщений: 302 Регистрация: 01.01.1970 Ternov

Kuzmich Пользователь Сообщений: 8000 Регистрация: 21.12.2012

#5 03.02.2021 18:56:25 Код Sub iWordRed() Dim i As Long Dim j As Integer Dim n As Integer Dim iLR As Long Dim iLastRow As Long Dim re As Object Dim objMatch As Object   iLR = Cells(Rows.Count, "E").End(xlUp).Row   iLastRow = Cells(Rows.Count, "C").End(xlUp).Row       Range("C2:C" & iLastRow).Font.ColorIndex = 0       Range("B2:B" & iLastRow).ClearContents For n = 3 To iLR       Set re = CreateObject("VBScript.RegExp")           re.Global = True           re.IgnoreCase = True           re.Pattern = Cells(n, "E")   For i = 2 To iLastRow     If re.test(Cells(i, "C")) Then         Set objMatch = re.Execute(Cells(i, "C"))(0)             Cells(i, "B") = objMatch           With Cells(i, "C").Characters(Start:=objMatch.FirstIndex + 1, Length:=objMatch.Length).Font                 .ColorIndex = 3           End With     End If   Next       Set re = Nothing Next End Sub

ternovsky Пользователь Сообщений: 302 Регистрация: 01.01.1970 Ternov

#6 03.02.2021 19:08:45 vikttur, перенес формулу в гугл ексель Код =ArrayFormula(ИНДЕКС(ПРОМО;МАКС(ЕСЛИ(ЕЧИСЛО(ПОИСК(ПРОМО;S2));СТРОКА(ПРОМО)-1)))) ПРОМО = это именованный диапазон на другом листе и выводит ошибку: пишет Ошибка№1 «Аргумент Параметр 1 в функции MINUS поддерживает только значения типа «число». Тип значения «Промо» – текст, поэтому его нельзя привести к типу «число».» Ошибка№2 «Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне D25.

ternovsky Пользователь Сообщений: 302 Регистрация: 01.01.1970 Ternov	Kuzmich, а этот макрос в гугл ексель получится вставить))

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	#8 03.02.2021 20:20:59 Цитата ternovsky написал:  этот макрос в гугл ексель получится вставить …если знаете и VBA, и  JS и сумеете адаптировать его к другому языку О формуле. Писал для Excel и в Excel… Да и функции MINUS в Вашей формуле не видно.

Alex T. Пользователь Сообщений: 63 Регистрация: 12.08.2020	#9 03.02.2021 20:30:56 А если вдруг гугл не личный, а корпоративный, причем уровня Enterprise по моему — можно воспользоваться их конвертером для избранных

admin

• В этой теме 17 ответов, 10 участников, последнее обновление 2 года, 11 месяцев назад сделано suprun.work.

Просмотр 3 сообщений — с 16 по 18 (из 18 всего)

• Автор

  Сообщения

• Ошибка
  Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне Q2.
  Ошибка!
  Укажите допустимую дату.

  Работает формула только один раз. Когда возвращаешься на этот лист, то ошибка

  Лист надо добавить в исключение

  Автоматические функции в таблице

  Пометьте лист как без авт. форматирования. И затем пропишите формулу.

  01.07.2020 в 16:26

  #11528

  А как собрать в отдельном листе незабранные заказы сразу с нескольки листов? К примеру начался новый месяц, но еще висят заказы с предыдущего месяца

• Автор

  Сообщения

Просмотр 3 сообщений — с 16 по 18 (из 18 всего)

• Для ответа в этой теме необходимо авторизоваться.

Некоторые гугл-формулы, такие как: Filter, Importrange и т.п., возвращают массив, который может вызвать конфликт диапазонов: «Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне X». Дабы избежать данной ошибки, есть ли возможность в коде скрипта проверить размер возвращаемого формулой результата?

Желательно без «Создать страницу — вызвать там — определить размер — удалить страницу». Была также идея проверять ячейку функции на «#ССЫЛ!» с помощью .getDisplayValue(), при проставлении рандомного значения в предполагаемый диапазон ее результата. Но реализация с постоянными обращениями к таблице через .setValue(), .getValue() и т.д. выйдет «дорогой» по времени исполнения скрипта. Прошу подсказать более изящное решение. Заранее благодарен!

• google-apps-script
• google-spreadsheet
• googlesheets

Отслеживать
задан 24 апр 2022 в 5:59
Alex Koltc Alex Koltc

Быстрее и правильнее именно setValues() и getValues(). 1 раз считали данные. Отфильтровали скриптом. 1 раз записали данные.

05. Google таблицы. Функции ArrayFormula и ARRAY_CONSTRAIN

24 апр 2022 в 7:52

Обычно, вставляя формулу вы знаете занимаемый ею размер. Как минимум это видно по передаваемым диапазонам или генерируемым последовательностям. Т.о. размер диапазона всегда известен. Но я не знаю готовых программных алгоритмов, позволяющих по тексту формулы определить этот размер.

Источник: ru.stackoverflow.com

Как разрешить перезапись результатов функции Filter?

хочу как то редактировать результаты определенной колонки массива функции filter так, чтобы эта колонка редактировалась и в первоисточнике, то есть в обход ошибки «Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне X.»

• Вопрос задан более года назад
• 121 просмотр

Комментировать
Решения вопроса 1

Google Products Expert

Этого нельзя сделать без внешних инструментов, т.к. нарушает принципы работы электронных таблиц.

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

Источник: qna.habr.com

FILTER

Функция FILTER Таблиц Гугл отображает только те строки или столбцы в диапазоне, которые соответствуют заданным условиям. Отличительной особенностью этой формулы является то, что она работает как со строками, так и с столбцами.

Google таблицы. Динамическая генерация данных в диапазоне. Функции ArrayFormula&Offset Урок 29.

Примечание Это большой и подробный указатель на возможности формулы FILTER на русском языке. Все примеры доступны в Таблице, см. раздел Ссылки .

Описание

Функция FILTER универсальная и очень гибкая. Она очень быстрая и легко читается. Используя такие функции как REGEXMATCH и MATCH , можно имитировать работу SQL-подобных запросов типа like и выбора на основе списка. Это действительно удобно, когда требуется отобрать большое количество данных, не зная конечного числа аргументов в уловии отбора.

Для всех примеров будет использоваться следующий набор данных

Примеры

Колонка соответсвует конкретному значению

=FILTER(‘Данные’!A2:F18;’Данные’!F2:F18=»A»)

Данные содержат заданную подстроку

Вывести все имена, в которых есть буквосочетание ль

=FILTER(‘Данные’!A2:F;REGEXMATCH(‘Данные’!B2:B; «ль»))

Данные начинаются с определенной строки

Вывести имена, которые начинаются на А

=FILTER(‘Данные’!A2:F;REGEXMATCH(‘Данные’!B2:B; «^А»))

Данные не начинаются с определенной строки

Вывести имена не начинающиеся на А

=FILTER(‘Данные’!A2:F;REGEXMATCH(‘Данные’!B2:B; «^[^А]»))

Сравнение чисел

Вывести данные, числа которых меньше 2500

=FILTER(‘Данные’!A2:F;’Данные’!D2:D<2500)

Числа содержат определенную цифру

Номер содержит цифру 2

=FILTER(‘Данные’!A2:F;REGEXMATCH(TEXT(‘Данные’!A2:A; «0»); «2»))

Числа оканчиваются на определенную цифру

Номер оканчивается на цифру 2

=FILTER(‘Данные’!A2:F;REGEXMATCH(TEXT(‘Данные’!A2:A; «0»); «2$»))

Сравнение дат

Выбрать всё до даты 31.12.2017

=FILTER(‘Данные’!A2:F;’Данные’!C2:C<=DATE(2017;12;31))

Даты только определенного года

Только 2017 год

=FILTER(‘Данные’!A2:F;REGEXMATCH(TEXT(‘Данные’!C2:C; «yyyyMMdd»); «2017»))

Даты только определенного месяца

Только август 2019

=FILTER(‘Данные’!A2:F;EOMONTH(‘Данные’!C2:C;1)=EOMONTH(DATE(2019;8;1);1))

=FILTER(‘Данные’!A2:F;MONTH(‘Данные’!C2:C)=8)

Отбор по счету (по очереди)

Выбрать каждую шестую строку

=FILTER(‘Данные’!A2:F; NOT(MOD(ROW(‘Данные’!A2:A)-1; 6)))

Выбрать каждую нечетную строку

=FILTER(‘Данные’!A2:F; ISODD(ROW(‘Данные’!A2:A)-1))

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

Выбрать данные, имена которых находятся в списке

=FILTER(‘Данные’!A2:F;IFERROR(MATCH(‘Данные’!B2:B;;0);))

Данные, которые соответствуют списку

Выбрать имена, которые находятся в списке. Список в I8:I12

=FILTER(‘Данные’!A2:F;MATCH(‘Данные’!B2:B;I8:I12;0))

Данные, которые отсутствуют в списке

Выбрать имена, которых нет в списке. Список в I15:I19

=FILTER(‘Данные’!A2:F;ISNA(MATCH(‘Данные’!B2:B;I15:I19;0)))

Данные, которые соответствуют “нечеткому” списку значений

Выбрать все, в поле Строка которых, есть последовательность символов “x” и, любая цифра или тире “-”

=FILTER(‘Данные’!A2:F;REGEXMATCH(‘Данные’!E2:E;»x[d-]»))

Вывести все данные, где поле Имя начинается с буквы из списка

Список находится в I9:I12

=FILTER(‘Данные’!A2:F;REGEXMATCH(‘Данные’!B2:B; «(?i)^(» |»;1; I9:I12) )»))

Пример выбора женских имен

=FILTER(‘Данные’!A2:F;REGEXMATCH(‘Данные’!B2:B; «(?i)(» |»;1; I15:I19) )»))
=FILTER(‘Данные’!A2:F;REGEXMATCH(‘Данные’!B2:B; «(?i)(» |»; ) )»))

Пример выбора мужских имен

=FILTER(‘Данные’!A2:F;NOT(REGEXMATCH(‘Данные’!B2:B; «(?i)(» |»;1;I23:I27) )»)))

Выборка с условием ИЛИ

Требуется отфильтровать данные в Дате которых указан 2019 год или Группа которых равна «B»

=FILTER(‘Данные’!A2:F;(YEAR(‘Данные’!C2:C)=2019) + (‘Данные’!F2:F=»B»))

Бесконечные диапазоны

Бесконечные диапазоны в FILTER могут создавать некоторые неудобства, особенно, если в выборку попадает достаточно много пустых строк. Они просто занимают место в результирующей Таблице. Если на это место внести данные, то получим ошибку

Ошибка Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне.

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

В примере ниже на листе Данные 1000 строк, из которых только 20 заполнены значениями. Вот результаты фильтров

=FILTER(‘Данные’!A2:F;YEAR(‘Данные’!C2:C)»»)
=FILTER(‘Данные’!A2:F;YEAR(‘Данные’!C2:C)<2018)

В первой формуле учитываются и не выводятся пустые строки ‘Данные’!A2:A<>»»

Фильтр по колонкам

Фильтр по колонкам проверяет условие в горизонтальном массиве. Если значение условно истинное, то колонка выведется, иначе нет.

Жестко заданные колонки по номерам. Только вторая и шестая колонка

=FILTER(‘Данные’!A1:F;<011>)

Колонки по определенному условию. Если имя колонки равно “Имя” или “Группа”

=FILTER(‘Данные’!A1:F;ARRAYFORMULA(REGEXMATCH(‘Данные’!A1:F1;»(?i)(имя|группа)»)))

Динамический фильтр колонок

В диапазоне F2:F7 находятся условия для фильтра

=FILTER(‘Данные’!A1:F;TRANSPOSE(F2:F7))

Ссылки

• FILTER — Cправка — Редакторы Документов
• Примеры формул в Таблице

Источник: contributor.pw