Автоматичне розширення моделей
Свайпніть щоб показати меню
Таблиці 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 є нестабільною функцією?
Дякуємо за ваш відгук!
Запитати АІ
Запитати АІ
Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат
Автоматичне розширення моделей
Таблиці 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 оновлюються автоматично;
- Іменовані діапазони продовжують динамічно розширюватися.
Дякуємо за ваш відгук!
