Суть такая есть 2 таблицы со связанными данными между собой, Ввод связанных данных производится массивом (В основном {"Название колонки";ArrayFormula(VLOOKUP())}). Данные в таблице 2 никак не влияют на таблицу 1, а Данные таблицы 1 выводятся в таблицу 2 в зависимости от введенных данных в один из столбцов. Как пример если в столбце А2 поставить 1, в столбце В2 будет номер 34528, а если в А2 поставить 2 в В2 значение поменяется на 6985, и т.д. в зависимости от заполненности таблицы 2. Проблема в том что иногда приходится тасовать строки местами, при этом формула массива считает что в столбец В ввели данные, и массив выдает REF (Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне B2.) Как построить функцию массива так, чтобы она перезаписывала данные в нужном ей диапазоне, даже если кто то вручную введет там данные (они не нужны и вводятся по ошибке) Пример таблицы: Формула вбита в В1, если перетасовать Строки она слетает, но если удалить значения в колонке В все работает опять
Суть такая есть 2 таблицы со связанными данными между собой, Ввод связанных данных производится массивом (В основном {"Название колонки";ArrayFormula(VLOOKUP())}). Данные в таблице 2 никак не влияют на таблицу 1, а Данные таблицы 1 выводятся в таблицу 2 в зависимости от введенных данных в один из столбцов. Как пример если в столбце А2 поставить 1, в столбце В2 будет номер 34528, а если в А2 поставить 2 в В2 значение поменяется на 6985, и т.д. в зависимости от заполненности таблицы 2. Проблема в том что иногда приходится тасовать строки местами, при этом формула массива считает что в столбец В ввели данные, и массив выдает REF (Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне B2.) Как построить функцию массива так, чтобы она перезаписывала данные в нужном ей диапазоне, даже если кто то вручную введет там данные (они не нужны и вводятся по ошибке) Пример таблицы: Формула вбита в В1, если перетасовать Строки она слетает, но если удалить значения в колонке В все работает опять
А что Вы подразумеваете под тасовкой? Если отсортировать полные строки хоть по колонке A, хоть по колонке B, то все сортируется нормально.
Если цеплять мышкой какую-нибудь ячейку в колонке A и перетаскивать ее в новое место в колонке A (на другую строку), то соответствующее значение в колонке B "следует" за своим аргументом из колонки A.
Делать подобные перетаскивания в колонке B не получится - по понятным причинам.
Если Вам надо придать какую-то последовательность значениям в колонке B, то можно скопировать значения из колонок A:B, скажем, в колонки D:E и там цеплять мышкой уже не одну ячейку, а две соседние в колонках D:E. Допустим, выделяете диапазон D2:E2 и перетаскиваете его в D9:E9 и т.д. с другими строками. Когда закончите, копируете колонку D в колонку A, а колонки D:E очищаете.
А что Вы подразумеваете под тасовкой? Если отсортировать полные строки хоть по колонке A, хоть по колонке B, то все сортируется нормально.
Если цеплять мышкой какую-нибудь ячейку в колонке A и перетаскивать ее в новое место в колонке A (на другую строку), то соответствующее значение в колонке B "следует" за своим аргументом из колонки A.
Делать подобные перетаскивания в колонке B не получится - по понятным причинам.
Если Вам надо придать какую-то последовательность значениям в колонке B, то можно скопировать значения из колонок A:B, скажем, в колонки D:E и там цеплять мышкой уже не одну ячейку, а две соседние в колонках D:E. Допустим, выделяете диапазон D2:E2 и перетаскиваете его в D9:E9 и т.д. с другими строками. Когда закончите, копируете колонку D в колонку A, а колонки D:E очищаете.Gustav
В гугл докс можно цеплять всю строку и перенести в то положение которое нужно. Таблица имеет большое количество данных, ваш вариант мне не подходит. Я ищу решение именно чтобы массив сам мог перезаписывать данные. На текущий момент создал кнопку со скриптом, который удаляет все ошибочно введенные данные в область действия массива. Но данное решение не удовлетворяет вопросам актуальности, если вовремя ошибку не увидел оператор и не нажал кнопку массив REFается и данные исчезают
В гугл докс можно цеплять всю строку и перенести в то положение которое нужно. Таблица имеет большое количество данных, ваш вариант мне не подходит. Я ищу решение именно чтобы массив сам мог перезаписывать данные. На текущий момент создал кнопку со скриптом, который удаляет все ошибочно введенные данные в область действия массива. Но данное решение не удовлетворяет вопросам актуальности, если вовремя ошибку не увидел оператор и не нажал кнопку массив REFается и данные исчезаютKashimirush
Ну, если целыми строчками двигаете, тогда можно не использовать одну формулу массива в B1, а "по старинке" иметь отдельную формулу для каждой строки, начиная с B2 и далее вниз: [vba]
Код
=IFERROR(VLOOKUP(A2;'Лист2'!A:B;2;FALSE);"")
[/vba] И таскайте полные строчки себе на здоровье! Формулы массива - вешь, несомненно, удобная и в ряде случаев даже очень полезная, но на них свет клином не сошелся.
Ну, если целыми строчками двигаете, тогда можно не использовать одну формулу массива в B1, а "по старинке" иметь отдельную формулу для каждой строки, начиная с B2 и далее вниз: [vba]
Код
=IFERROR(VLOOKUP(A2;'Лист2'!A:B;2;FALSE);"")
[/vba] И таскайте полные строчки себе на здоровье! Формулы массива - вешь, несомненно, удобная и в ряде случаев даже очень полезная, но на них свет клином не сошелся.Gustav
Так изначально и было, но таблицей пользуются порядка 15 человек Строки вниз доходят до 1000-2000 и далее, добавляются постепенно по 50-100 новых строк, и непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101, как раз поэтому и перешли на формулу массива. Скорее всего надо будет в дальнейшем переходить на создание базы данных, но это уже другая история. Проект создавали в спешке и дабы не тратить время на программистов начали работать в гугл докс.
Так изначально и было, но таблицей пользуются порядка 15 человек Строки вниз доходят до 1000-2000 и далее, добавляются постепенно по 50-100 новых строк, и непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101, как раз поэтому и перешли на формулу массива. Скорее всего надо будет в дальнейшем переходить на создание базы данных, но это уже другая история. Проект создавали в спешке и дабы не тратить время на программистов начали работать в гугл докс.Kashimirush
непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101
Ну, почему непонятным, очень даже понятным: случайно (думаю, все же не злонамеренно) перетаскивают ячейку в колонке A в другую строку - вот и начало "разъезда" формул по разным строкам.
Ну, хорошооо... А попробуйте-ка "повалить" аналогичным образом вот такую конструкцию для одной строки (взамен предыдущей моей формулы), тоже начиная с B2 и вниз: [vba]
непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101
Ну, почему непонятным, очень даже понятным: случайно (думаю, все же не злонамеренно) перетаскивают ячейку в колонке A в другую строку - вот и начало "разъезда" формул по разным строкам.
Ну, хорошооо... А попробуйте-ка "повалить" аналогичным образом вот такую конструкцию для одной строки (взамен предыдущей моей формулы), тоже начиная с B2 и вниз: [vba]
Gustav, Какая логика работы такого сочетания формулы? Минус формул еще в том что их постоянно нужно протягивать , т.к. мы изначально не знаем сколько строк будет в конечной таблице, операторы про это забывают и мне постоянно приходилось следить за таблицами и допротягивтаь формулы если операторы добавляли новые строки.
Gustav, Какая логика работы такого сочетания формулы? Минус формул еще в том что их постоянно нужно протягивать , т.к. мы изначально не знаем сколько строк будет в конечной таблице, операторы про это забывают и мне постоянно приходилось следить за таблицами и допротягивтаь формулы если операторы добавляли новые строки.Kashimirush
Gustav, Все таки формулы тоже не идеально решение, да теперь данные возвращаются верно всегда, но при добавлении или нечаянном перемещении ячейки, формула не прописывается в пустую ячейку и я буду опять иметь гемор по поиску ошибок и по допротягиванию формул.
Gustav, Все таки формулы тоже не идеально решение, да теперь данные возвращаются верно всегда, но при добавлении или нечаянном перемещении ячейки, формула не прописывается в пустую ячейку и я буду опять иметь гемор по поиску ошибок и по допротягиванию формул.Kashimirush
Работа, работа, перейди на Федота...
Сообщение отредактировал Kashimirush - Пятница, 05.07.2019, 08:24
Созрел вариант со скриптом к первоначальной формуле массива. Пишем такой маленький скриптик: [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.
Могу я в свой скрипт добавить как то время повторения допустим раз в час
Вот это как раз в сторону триггеров надо смотреть.
Созрел вариант со скриптом к первоначальной формуле массива. Пишем такой маленький скриптик: [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.
function T800() { var sheet = SpreadsheetApp.getActive(); var Trigger = sheet.getRange('B1').getValues(); if(Trigger=='#REF!'){ sheet.getRange('B2:B4000').clear({contentsOnly: true, skipFilteredRows: true}); } }
, в редакторе скриптов нажал кнопочку триггеры текущего проекта - выбросило на новую страницу, в правом нижнем углу нажал плюсик добавить триггер. Там в всплывающем окне выбрал "свою функцию"; "Основное развертывание"; "Из таблицы"; "При редактировании" Теперь при редактировании скрипт срабатывается если формула в В1 REF-ается и удаляет все не нужное в диапазоне В2:В4000 (почему то при указании диапазона В2:В - скрипт выдает ошибку, мне 4000 вполне хватит) Результат можете смотреть в шапке поста , там ссылка на тестовую таблицу.
function T800() { var sheet = SpreadsheetApp.getActive(); var Trigger = sheet.getRange('B1').getValues(); if(Trigger=='#REF!'){ sheet.getRange('B2:B4000').clear({contentsOnly: true, skipFilteredRows: true}); } }
, в редакторе скриптов нажал кнопочку триггеры текущего проекта - выбросило на новую страницу, в правом нижнем углу нажал плюсик добавить триггер. Там в всплывающем окне выбрал "свою функцию"; "Основное развертывание"; "Из таблицы"; "При редактировании" Теперь при редактировании скрипт срабатывается если формула в В1 REF-ается и удаляет все не нужное в диапазоне В2:В4000 (почему то при указании диапазона В2:В - скрипт выдает ошибку, мне 4000 вполне хватит) Результат можете смотреть в шапке поста , там ссылка на тестовую таблицу.Kashimirush
Работа, работа, перейди на Федота...
Сообщение отредактировал Kashimirush - Понедельник, 08.07.2019, 08:25
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
