Сколько времени вы тратите на редактирование диапазонов, на которые ссылаются формулы, потому что вы добавили новые данные в ваш источник?
Это то, что я говорю; допустим, у вас есть какие-то данные в столбцах А:Е:
И в столбцы G и H у вас есть резюме данных, в этом примере продаж по продавцам:
См. формулу в строке формул является фиксированной на ячейки B2:B19 и Е2:Е19. Когда я добавляю новые данные за сентябрь, чтобы мои данные в столбцах А:Е мне придется вручную редактировать формулы в столбец H, чтобы включить новые строки.
Позвольте мне просто сказать, если вы делаете такого рода вещи больше, чем один раз в любой книге, то вы теряете драгоценное время, и вы должны быть готовы к созданию формулы с динамическим диапазонами.
>Динамические диапазоны помогают автоматически обнаружить новые данные и включить их в свои формулы.
Забыть на VBA, это руки вниз один из лучших способов экономии времени при работе с Excel.
Динамические Ссылки
Существует несколько различных способов настройки диапазонов так, чтобы они динамически обновляются.
1. Весь Столбец/Строку Ссылки
Например, принимая данные выше можно заменить формулу в ячейке H2 с:
=СУММЕСЛИ($B:$B,G2,$Е:$Е)
Пока это будет работать, это самый легкий способ создания динамической ссылки и это не рекомендуется.
Почему? Потому что вы спрашиваете Excel, чтобы делать загрузку проверка работы клеток, которые являются пустыми, и в Excel 2007 и далее есть 1М+ клеток в каждом столбце. Суть заключается весь столбец/строку ссылки = медленном книги (обычно).
2. Таблицы Excel
Динамические ссылки-это только один из плюсов для нахождения ваших данных в таблицах Excel.
Совет: если формулы являются большой сложностью для вас, то таблицы Excel дадут Вам множество преимуществ без необходимости сложных формул.
3. Динамический Именованный Диапазон с помощью офсетной
Сейчас мы потихоньку становимся более искушенными.
Мы можем заменить формулу в примере выше с этим:
=СУММЕСЛИ(продавец,J2,OrderAmt)
Где ‘продавец’ и ‘OrderAmt’ являются динамические именованные диапазоны, как вы можете видеть в Диспетчер имен ниже:
Как создать динамический именованный диапазон
Настройка любого Именованного диапазона перейдите на вкладку Формулы ленты и нажмите кнопку "определить имя":
Мы можем сделать его динамичным путем ввода формулы в ‘означает:’ поля такой:
Подождите, давайте поближе рассмотрим эту формулу в ‘означает:’ поле:
=Лист1!$B$2:смещение(Лист1!$B$1,СЧЕТЗ(Лист1!$B$2:$B$1000),,1)
Первая часть в оранжевый ничего особенного, но вторая часть в голубой и розовый использует смещение и функции СЧЕТЗ возвращает ссылку на последнюю ячейку в диапазоне В2:В1000, содержащую данные.
Вот краткое напоминание о синтаксис функции смещ:
Смещение(ссылка,строк,столбцов,[высота],[ширина])
=Ссылка диапазон, который начинается в ячейке В2:, и использовать смещение и функции СЧЕТЗ, чтобы найти последнюю ячейку в диапазоне, который содержит данные в ячейках B2:В1000. В офсетной функции Start в ячейке B1, и подсчет всех ячеек в диапазоне В2:В1000, содержащие данные, (которые = 18 т. е. строки от 2 до 19), и возвращает ссылку на ячейку, 1 клетка высокая.
То есть, если я начну в ячейке B1 и отсчет 18 ячеек я в конечном итоге в ячейке В19 поэтому моя формула вычисляет такой:
=Лист1$B$2:$В$19
При добавлении новых данных после того, как ячейку В19 формулу будет динамически обновлять вернуть правильный диапазон, максимум до 1000 строк (потому что я только подсчет данных в диапазоне В2:В1000).
Примечания:
- Нельзя вставлять какие-либо пробелы в В2:В1000 поскольку функция СЧЕТЗ не будет считать эти клетки и в результате неправильного диапазона.
- Если вы рассчитываете числа можно заменить СЧЕТЗ функция с помощью функции count;, как я сделал для OrderAmt именованного диапазона (см. Диспетчер имен рисунке выше).
- Если вы думаете, что ваши данные будут превышать 1000 строк просто увеличить номер строки надлежащим образом обслуживать его.
Проблема со смещением
Теперь, в то время как динамический именованный диапазон готов, есть одна главная проблема с ним:
Смещение переменной функции, что означает, что каждый раз при редактировании любой ячейки, пересчитывая. Влияние это может оказать замедление работы книги, очень медленно.
Так медленно, что Вы не сохраняйте вашу работу, потому что это будет означать, что вы остаетесь бить баклуши. Вы в конечном итоге не экономя ваши работы потому что Вы не хотите развивать неприглядный мозоли, то ваш компьютер аварийно завершает работу.....и, ну, вы знаете остальное.
Поэтому следующий вариант является лучшим.
4. Динамический Именованный Диапазон с помощью индекса
Функции индекс является не только энергонезависимой, это быстрее, чем смещение функции.
Мы можем создать динамический именованный диапазон для менеджера по продажам столбец, используя индекс такой:
=Лист1!$B$2:индекс(Лист1!$B$2:$B$1000,СЧЕТЗ(Лист1!$B$2:$B$1000))
И для в столбце " сумма заказа такой:
=Лист1!$Е$2:индекс(Лист1!$Е$2:$е$1000,граф(Лист1!$Е$2:$Е$1000))
Синтаксис функции индекс в ссылочной форме:
Индекс(ссылка, row_num, [column_num], [area_num])
Примечание: последние два аргумента, [column_num] и [area_num] являются необязательными, как мы можем судить из квадратных скобках. Мы не нужны в этой формуле.
Я переведу сумма заказа формулу на русский язык:
=Ссылка диапазон, который начинается в ячейке E2:, затем индекс в диапазон Е2:Е1000, и вернуть row_num последней ячейки, содержащие данные, подсчет всех ячеек в диапазоне В2:В1000, которые содержат цифры, (которые = 18 т. е. ряды со 2 по 19), поэтому Индекс возвращает ссылку на Е19.
Моя формула вычисляет такой:
=Лист1$Е$2:$Е$19
Это всего лишь одна из уловок, которые в индексе шляпу. Когда объединился с матча он может заменить ВПР, на самом деле индекс и матч быстрее, чем ВПР тоже.
| 
