Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Как сделать массив с перезаписью - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Как сделать массив с перезаписью (Формулы/Formulas)
Как сделать массив с перезаписью
Kashimirush Дата: Пятница, 28.06.2019, 08:17 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 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
 
Ответить
СообщениеСуть такая есть 2 таблицы со связанными данными между собой, Ввод связанных данных производится массивом (В основном {"Название колонки";ArrayFormula(VLOOKUP())}).
Данные в таблице 2 никак не влияют на таблицу 1, а Данные таблицы 1 выводятся в таблицу 2 в зависимости от введенных данных в один из столбцов. Как пример если в столбце А2 поставить 1, в столбце В2 будет номер 34528, а если в А2 поставить 2 в В2 значение поменяется на 6985, и т.д. в зависимости от заполненности таблицы 2.
Проблема в том что иногда приходится тасовать строки местами, при этом формула массива считает что в столбец В ввели данные, и массив выдает REF (Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне B2.)
Как построить функцию массива так, чтобы она перезаписывала данные в нужном ей диапазоне, даже если кто то вручную введет там данные (они не нужны и вводятся по ошибке)
Пример таблицы:
Формула вбита в В1, если перетасовать Строки она слетает, но если удалить значения в колонке В все работает опять

Автор - Kashimirush
Дата добавления - 28.06.2019 в 08:17
Gustav Дата: Среда, 03.07.2019, 14:32 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1661
Репутация: 674 ±
Замечаний: 0% ±

начинал с Excel 4.0...
А что Вы подразумеваете под тасовкой? Если отсортировать полные строки хоть по колонке 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
 
Ответить
СообщениеА что Вы подразумеваете под тасовкой? Если отсортировать полные строки хоть по колонке A, хоть по колонке B, то все сортируется нормально.

Если цеплять мышкой какую-нибудь ячейку в колонке A и перетаскивать ее в новое место в колонке A (на другую строку), то соответствующее значение в колонке B "следует" за своим аргументом из колонки A.

Делать подобные перетаскивания в колонке B не получится - по понятным причинам.

Если Вам надо придать какую-то последовательность значениям в колонке B, то можно скопировать значения из колонок A:B, скажем, в колонки D:E и там цеплять мышкой уже не одну ячейку, а две соседние в колонках D:E. Допустим, выделяете диапазон D2:E2 и перетаскиваете его в D9:E9 и т.д. с другими строками. Когда закончите, копируете колонку D в колонку A, а колонки D:E очищаете.

Автор - Gustav
Дата добавления - 03.07.2019 в 14:32
Kashimirush Дата: Среда, 03.07.2019, 15:09 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А что Вы подразумеваете под тасовкой?

В гугл докс можно цеплять всю строку и перенести в то положение которое нужно.
Таблица имеет большое количество данных, ваш вариант мне не подходит.
Я ищу решение именно чтобы массив сам мог перезаписывать данные.
На текущий момент создал кнопку со скриптом, который удаляет все ошибочно введенные данные в область действия массива.
Но данное решение не удовлетворяет вопросам актуальности, если вовремя ошибку не увидел оператор и не нажал кнопку массив REFается и данные исчезают
 
Ответить
Сообщение
А что Вы подразумеваете под тасовкой?

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

Автор - Kashimirush
Дата добавления - 03.07.2019 в 15:09
Gustav Дата: Среда, 03.07.2019, 15:59 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1661
Репутация: 674 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Ну, если целыми строчками двигаете, тогда можно не использовать одну формулу массива в B1, а "по старинке" иметь отдельную формулу для каждой строки, начиная с B2 и далее вниз:
[vba]
Код
=IFERROR(VLOOKUP(A2;'Лист2'!A:B;2;FALSE);"")
[/vba]
И таскайте полные строчки себе на здоровье! Формулы массива - вешь, несомненно, удобная и в ряде случаев даже очень полезная, но на них свет клином не сошелся.


Мой tip box - яд 41001663842605
 
Ответить
СообщениеНу, если целыми строчками двигаете, тогда можно не использовать одну формулу массива в B1, а "по старинке" иметь отдельную формулу для каждой строки, начиная с B2 и далее вниз:
[vba]
Код
=IFERROR(VLOOKUP(A2;'Лист2'!A:B;2;FALSE);"")
[/vba]
И таскайте полные строчки себе на здоровье! Формулы массива - вешь, несомненно, удобная и в ряде случаев даже очень полезная, но на них свет клином не сошелся.

Автор - Gustav
Дата добавления - 03.07.2019 в 15:59
Kashimirush Дата: Четверг, 04.07.2019, 14:19 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
начиная с B2 и далее вниз:

Так изначально и было, но таблицей пользуются порядка 15 человек
Строки вниз доходят до 1000-2000 и далее, добавляются постепенно по 50-100 новых строк, и непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101, как раз поэтому и перешли на формулу массива.
Скорее всего надо будет в дальнейшем переходить на создание базы данных, но это уже другая история. Проект создавали в спешке и дабы не тратить время на программистов начали работать в гугл докс.
 
Ответить
Сообщение
начиная с B2 и далее вниз:

Так изначально и было, но таблицей пользуются порядка 15 человек
Строки вниз доходят до 1000-2000 и далее, добавляются постепенно по 50-100 новых строк, и непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 100 формула начинает поиск не в А100 а в А101, как раз поэтому и перешли на формулу массива.
Скорее всего надо будет в дальнейшем переходить на создание базы данных, но это уже другая история. Проект создавали в спешке и дабы не тратить время на программистов начали работать в гугл докс.

Автор - Kashimirush
Дата добавления - 04.07.2019 в 14:19
Gustav Дата: Четверг, 04.07.2019, 23:09 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1661
Репутация: 674 ±
Замечаний: 0% ±

начинал с Excel 4.0...
непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 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
 
Ответить
Сообщение
непонятным образом в коне концов в некоторых строках формула съезжает и это очень трудно отследить, допустим на строке 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]

Автор - Gustav
Дата добавления - 04.07.2019 в 23:09
Kashimirush Дата: Пятница, 05.07.2019, 08:09 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav, Какая логика работы такого сочетания формулы?
Минус формул еще в том что их постоянно нужно протягивать , т.к. мы изначально не знаем сколько строк будет в конечной таблице, операторы про это забывают и мне постоянно приходилось следить за таблицами и допротягивтаь формулы если операторы добавляли новые строки.
 
Ответить
СообщениеGustav, Какая логика работы такого сочетания формулы?
Минус формул еще в том что их постоянно нужно протягивать , т.к. мы изначально не знаем сколько строк будет в конечной таблице, операторы про это забывают и мне постоянно приходилось следить за таблицами и допротягивтаь формулы если операторы добавляли новые строки.

Автор - Kashimirush
Дата добавления - 05.07.2019 в 08:09
Kashimirush Дата: Пятница, 05.07.2019, 08:14 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav, Все таки формулы тоже не идеально решение, да теперь данные возвращаются верно всегда, но при добавлении или нечаянном перемещении ячейки, формула не прописывается в пустую ячейку и я буду опять иметь гемор по поиску ошибок и по допротягиванию формул.


Сообщение отредактировал Kashimirush - Пятница, 05.07.2019, 08:24
 
Ответить
СообщениеGustav, Все таки формулы тоже не идеально решение, да теперь данные возвращаются верно всегда, но при добавлении или нечаянном перемещении ячейки, формула не прописывается в пустую ячейку и я буду опять иметь гемор по поиску ошибок и по допротягиванию формул.

Автор - Kashimirush
Дата добавления - 05.07.2019 в 08:14
Kashimirush Дата: Пятница, 05.07.2019, 08:20 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Могу я в свой скрипт добавить как то время повторения допустим раз в час

 
Ответить
СообщениеМогу я в свой скрипт добавить как то время повторения допустим раз в час


Автор - Kashimirush
Дата добавления - 05.07.2019 в 08:20
Kashimirush Дата: Пятница, 05.07.2019, 10:00 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А еще круче былоб если скрипт начинал действовать, если формула в одной из ячейке выдавала бы REF
 
Ответить
СообщениеА еще круче былоб если скрипт начинал действовать, если формула в одной из ячейке выдавала бы REF

Автор - Kashimirush
Дата добавления - 05.07.2019 в 10:00
Gustav Дата: Пятница, 05.07.2019, 18:37 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1661
Репутация: 674 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Созрел вариант со скриптом к первоначальной формуле массива. Пишем такой маленький скриптик:
[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
 
Ответить
СообщениеСозрел вариант со скриптом к первоначальной формуле массива. Пишем такой маленький скриптик:
[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.
Могу я в свой скрипт добавить как то время повторения допустим раз в час

Вот это как раз в сторону триггеров надо смотреть.

Автор - Gustav
Дата добавления - 05.07.2019 в 18:37
Kashimirush Дата: Понедельник, 08.07.2019, 07:38 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav, TypeError: Не удается прочитать свойство "changeType" объекта undefined. (строка 4, файл T2)
Ругается на 4 строку:
if (e.changeType == 'INSERT_ROW') {
 
Ответить
СообщениеGustav, TypeError: Не удается прочитать свойство "changeType" объекта undefined. (строка 4, файл T2)
Ругается на 4 строку:
if (e.changeType == 'INSERT_ROW') {

Автор - Kashimirush
Дата добавления - 08.07.2019 в 07:38
Kashimirush Дата: Понедельник, 08.07.2019, 07:51 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Gustav,
Я написал в выходные такой скрипт

Но он не инициируется автоматически нужно его запускать, где про триггеры почитать лучше?
 
Ответить
СообщениеGustav,
Я написал в выходные такой скрипт

Но он не инициируется автоматически нужно его запускать, где про триггеры почитать лучше?

Автор - Kashimirush
Дата добавления - 08.07.2019 в 07:51
Kashimirush Дата: Понедельник, 08.07.2019, 08:23 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Вроде разобрался, оставил свой скрипт

, в редакторе скриптов нажал кнопочку триггеры текущего проекта - выбросило на новую страницу, в правом нижнем углу нажал плюсик добавить триггер.
Там в всплывающем окне выбрал "свою функцию"; "Основное развертывание"; "Из таблицы"; "При редактировании"
Теперь при редактировании скрипт срабатывается если формула в В1 REF-ается и удаляет все не нужное в диапазоне В2:В4000 (почему то при указании диапазона В2:В - скрипт выдает ошибку, мне 4000 вполне хватит)
Результат можете смотреть в шапке поста , там ссылка на тестовую таблицу.


Сообщение отредактировал Kashimirush - Понедельник, 08.07.2019, 08:25
 
Ответить
СообщениеВроде разобрался, оставил свой скрипт

, в редакторе скриптов нажал кнопочку триггеры текущего проекта - выбросило на новую страницу, в правом нижнем углу нажал плюсик добавить триггер.
Там в всплывающем окне выбрал "свою функцию"; "Основное развертывание"; "Из таблицы"; "При редактировании"
Теперь при редактировании скрипт срабатывается если формула в В1 REF-ается и удаляет все не нужное в диапазоне В2:В4000 (почему то при указании диапазона В2:В - скрипт выдает ошибку, мне 4000 вполне хватит)
Результат можете смотреть в шапке поста , там ссылка на тестовую таблицу.

Автор - Kashimirush
Дата добавления - 08.07.2019 в 08:23
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Как сделать массив с перезаписью (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!