Автоматичне розширення моделей
Свайпніть щоб показати меню
Таблиці Excel автоматично розширюють структуровані посилання, але деякі обчислення все ще залежать від стандартних діапазонів. У цьому розділі розглядається створення динамічних діапазонів, які автоматично зростають разом із набором даних.
Структура COUNTA
=COUNTA(value1, [value2], ...)
value1: перший діапазон або значення для підрахунку;[value2]: додаткові необов'язкові діапазони або значення.
COUNTA підраховує всі непорожні клітинки.
Формула, використана в цьому розділі:
=COUNTA(Sales_Data[Revenue])
Повертає поточну кількість заповнених рядків Revenue.
Структура OFFSET
=OFFSET(reference, rows, cols, [height], [width])
reference: початкова клітинка;rows: вертикальне зміщення;cols: горизонтальне зміщення;[height]: висота повернутого діапазону;[width]: ширина повернутого діапазону.
OFFSET створює діапазон динамічно відносно початкової позиції.
Динамічна формула OFFSET
=SUM(OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1))
Sales_Data!$H$1: початкове посилання;1: пропускає рядок заголовка;COUNTA(...) - 1: динамічна висота набору даних;1: ширина повернутого діапазону.
Формула автоматично підлаштовується при додаванні нових рядків.
На аркуші Summary введіть:
=COUNTA(Sales_Data[Revenue])
Переконайтеся, що результат відповідає поточній кількості рядків Revenue.
Введіть:
=SUM(OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1))
Тепер підрахунок загальної суми Revenue динамічно розширюється разом із набором даних.
Додайте новий рядок транзакції у Sales_Data.
Переконайтеся, що підсумок Revenue оновлюється автоматично.
Відкрийте Диспетчер імен і створіть:
DynamicRevenue
Призначте таку формулу:
=OFFSET(Sales_Data!$H$1,1,0,COUNTA(Sales_Data!$H:$H)-1,1)
Замініть попередню формулу на:
=SUM(DynamicRevenue)
Логіка залишається динамічною, але формула стає простішою для читання та супроводу.
Створіть ще один іменований діапазон:
DynamicProfit
Призначте:
=OFFSET(Sales_Data!$J$1,1,0,COUNTA(Sales_Data!$J:$J)-1,1)
Введіть:
=SUM(DynamicProfit)
Додайте додаткові рядки у Sales_Data.
Переконайтеся, що:
- Підсумки Revenue оновлюються автоматично;
- Підсумки Profit оновлюються автоматично;
- Іменовані діапазони продовжують динамічно розширюватися.
1. Яка основна мета поєднання COUNTA з OFFSET?
2. Чому іменовані діапазони корисні при роботі з OFFSET?
3. Що означає, що OFFSET є нестабільною функцією?
Дякуємо за ваш відгук!
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
