Створення таблиць підстановки 4 страница
НОРМА – визначає вигідність надання позики, тобто реальну процентну ставку від надання позики на певну суму за умови фіксованих періодичних виплат протягом леякої кількості періодів. Потрібно задавати деяке початкове наближення до шуканої процентої ставки, наприклад, 0,1 (10%).
Синтаксис функції:
НОРМА (кількість періодів; періодична виплата; сума позики; кінцеве значення позики; тип операції; початкове наближення)
ПЗ – обчислює сьогоднішню вартість низки майбутніх надходжень (ренти) від бізнесу.
Синтаксис функції:
ПЗ – (депозитна процентна ставка; кількість періодів; рента за один період; рента в кінці терміну; тип операції).
Депозитна процентна ставка - це ставка, яку банк виплачує за вклади клієнтів. Для обчислення сьогоднішньої вартості майбутньої ренти використовується принцип дисконтування – приведення суми за деякий термін до її вартості в даний момент часу. Суттєво враховується депозитна процентна ставка, оскільки вважається, що вже перше надходження стає депозитом в банку. Дисконтування дає відповідь на запитання: чи варто вкладати гроші в даний бізнес, чи краще їх поставити в банк під відсотки і нічого не робити.
НЗП – обчислює сьогоднішню вартість різних рент, які поступають в кінці рівномірних періодів.
Синтаксис функції:
НЗП (депозитна процентна ставка; рента1; рента2, …)
Використовуючи описані вище функції можна розв’язати ряд задач.
Задача 1
Інвестор вкладає в банку рахунок на 2000 грн. на умовах 5% ставки прибутку щомісяця. Яка вартість інвестиції через 36 місяців?
Розв’язок задачі дає формула:
= БЗ (5%; 36;; -2000)
Відповідь: 11 583,63 грн.
Задача 2
Фірма інвестує 2000 грн. за умови таких щомісячних процентних ставок: 7%, 6%, 5%, 4%, 4%, 4% протягом шести місяців. Яка вартість інвестиції через 6 місяців? Яка вартість інвестицій, якщо покласти гроші на рахунок в банк під 5% на 6 місяців?
Розв’язок задачі дають формули:
= БЗРАСПИС (2000; {0,07; 0,06; 0,05; 0,04; 0,04; 0,04})
= БЗ (5%; 6;;-2000)
Відповідь: 2 679,22 грн.; 2 680,19 грн. Отже, вигідніше покласти гроші в банк, ніж вкласти в це бізнес.
Задача 3
Бізнесмен взяв у банку кредит на суму 2000 грн. терміном на 12 місяців за умови щомісячного погашення позики і місячної ставки кредиту 6%. Визначити величину щомісячних виплат і її складові в кінці першого місяця.
Розв’язок задачі дають формули:
= ППЛАТ (6%, 12; 2000)
= ПЛПРОЦ (6%; 1; 12; 2000)
= ОСНПЛАТ (6%; 1; 12; 2000)
Відповідь: -238,55 грн., -120,00 грн.; -118,55 грн.
Задача 4
Позику 2000 грн. беруть за умови повертання в кінці кожного місяця 200 грн. і процентної ставки 6%. Скільки місяців потрібно для повертання позики?
Розв’язок задачі дає формула:
= КПЕР (6; -200; 2000)
Відповідь: 15,73 місяців.
Задача 5
Бізнесмен звертається в банк за кредитом на суму 2000 грн. на 12 місяців за умови періодичних виплат 200 грн. протягом року щомісяця. Визначити процентну ставку позики.
Розв’язок задачі дає формула:
= НОРМА (12; -200; 2000; 0; 0; 0,1)
Відповідь: 3%.
Задача 6
В бізнес потрібно вкласти 25 000 грн. в кінці першого місяця потрібно вкласти ще 2000 грн., а в наступні п’ять місяців бізнес принесе такі доходи: 4 000, 5 000, 6 000, 7 000, 8 000 грн. Чи є цей бізнес вигідний?
Розв’язок задачі дає формула:
= НПЗ (%5; -2000; 4000; 5000; 6000; 7000; 8000)
Відповідь: Вартість бізнесу 22 433 грн. Оскільки затрати 25 000 грн. більші за вартість бізнесу, то такий бізнес не є вигідний.
Задача 7
Для ведення бізнесу потрібно вкласти 3500 грн., а бізнес протягом 5 місяців приноситиме по 1000 грн. доходу (ренти) в кінці місяця. Депозитна ставка банку 5%. Чи варто займатися цим бізнесом?
Розв’язок задачі дає формула:
= ПЗ (5%; 5; 1000)
Відповідь: вартість бізнесу є 4 329 грн. Оскільки цю суму потрібно вкладати, число отримується від’ємне. Бізнес вигідний, бо для його ведення потрібно лише 3500 грн. Якби було більше, ніж 4 329 грн., то такий бізнес був би збитковим.
Хід роботи
Розв’язати наступні задачі, використовуючи потрібні формули.
1. Для ведення бізнесу потрібно вкласти 3500 грн., в кінці терміна отримається рента 4500 грн. Депозитна ставка банку 5%. Чи варто займатися цим бізнесом?
2. Бізнесмен звертається в банк за кредитом на суму 15 000 грн. на 24 місяці за умови періодичних виплат 1000 грн. щомісяця. Визначити процентну ставку позики.
3. Позику 5000 грн. беруть за умови повертання в кінці кожного місяця 500 грн. і процентної ставки 6%. Скільки місяців потрібно для повертання позики?
4. Клієнт відкриває рахунок в банку, кладе 3000 грн. на 5% і докладатиме в кінці кожного місяця 200 грн. яка сума буде на рахунку через 12 місяців?
5. Підприємець бере позику 4000 грн. у банку під 6% терміном на 4 місяці. Визначити щомісячну виплату і її складові у всіх місяцях.
Контрольні запитання
1. Для чого призначені фінансові функції?
2. Що означає принцип дисконтування?
3. Яке призначення функції БЗ?
4. Яке призначення функції БЗРАСПИС?
5. Яке призначення функції ППЛАТ?
6. Яке призначення функції ПЛПРОЦ?
7. Яке призначення функції ОСНПЛАТ?
8. Яке призначення функції КПЕР?
9. Яке призначення функції НОРМА?
10. Яке призначення функції ПЗ?
Лабораторна робота 9
Тема. Робота зі списками. Сортування та фільтрація даних.
Мета. Навчити створювати списки з використанням форми. Навчити сортувати та фільтрувати дані. Навчити обчислювати проміжні підсумки.
Учні повинні знати: поняття списку, використання форми вводу, використання сортування та фільтрація даних.
Учні повинні уміти: створювати список, використовувати форму вводу, виконувати сортування та фільтрацію даних.
Програмне забезпечення: табличний процесор Microsoft Ехсеl.
Теоретичні відомості
Робота зі списками
В Excel списком називають базу даних, що розміщена на робочому аркуші. Розміри такого списку обмежені розмірами одного робочого аркушу. Зазвичай список має рядок заголовку (перший рядок списку, що містить опис даних) та рядки даних. Стовпці списку називають полями, а рядки — записами.
Як приклад розглянемо невеликий список книг відомих авторів.
Список, наведений на рисунку, складається з п'яти полів (№, Автор, Найменування, Кількість та Дата появи в магазині) і двадцяти двох записів (не враховуючи рядка заголовку).
За необхідності список можна розширити, додавши нові поля або записи. Проте треба пам’ятати, що список може містити не більше ніж 256 полів та 65 535 записів, тому що розмір робочого аркуша є обмеженим.
Поля списку повинні містити однотипну інформацію (текст, дати або числові значення). В списках не варто залишати порожніми рядки чи стовпці, а також поміщати додаткову інформацію в комірки, розміщені ліворуч чи праворуч від списку. Також слід уникати додаткових пробілів на початку та наприкінці комірок з даними
В Excel зі списками можна виконувати будь-які з перерахованих далі операцій:
• заповнювати список даними;
• фільтрувати списки відповідно до визначених критеріїв;
• сортувати дані в списку;
• обчислювати проміжні підсумки на основі необхідних
• функцій;
• створювати підсумкові зведені таблиці на основі інформації зі списку.
Введення інформації в список
В Excel існує декілька способів введення даних у список:
• введення інформації вручну;
• імпортування чи копіювання їх з іншого документа;
• використання для заповнення списку форму вводу даних.
Введення інформації вручну виконується відповідно до стандартних правил введення даних. Операції імпортування та копіювання даних з інших програм розглядалися на початку цього розділу. В наступному підрозділі мова йтиме про роботу з формами вводу.
Створення і використання форми вводу
Використання форми вводу даних дозволяє спростити і прискорити створення списку.
Форма вводу – це спеціальне діалогове вікно, в якому можна ввести або відобразити відразу цілий рядок даних зі списку.
Щоб створити форму вводу для нового списку, треба виконати такі дії:
1) Ввести у відповідні комірки робочого аркуша рядок заголовку, а потім — перший рядок даних, тобто перший запис списку.
2) Відформатувати заповнені комірки так, як повинні бути відформатовані поля списку.
3) Встановити табличний курсор у будь-якій із заповнених комірок та вибрати команду Данные/Форма. На основі попередньо введеної інформації Excel створить форму для вводу даних у список і відобразить її на екрані. У цій формі будуть показані дані з першого запису.
4) Щоб додати новий запис, треба натиснути кнопку Добавить. Тепер у порожні поля форми можна буде ввести нові дані. Після введення нового запису потрібно натиснути клавішу Enter або кнопку Добавить, щоб ввести наступний запис.
5) Після завершення додавання записів, треба клацнути на кнопці Закрыть, щоб закрити форму вводу.
Форма вводу служить не тільки для додавання даних до списку. Вона також дозволяє полегшити роботу зі створеним списком. За допомогою форми можна переглядати, редагувати, видаляти наявні записи, а також знаходити записи, що відповідають певним умовам.
Щоб відобразити на екрані форму для готового списку, потрібно встановити табличний курсор у будь-якому місці списку та виберіть команду Данные/Форма. Для переміщення по записах списку використовується смуга прокрутки форми, кнопки Найти назад і Найти далее, а також клавіші ←, →, ↓, ↑, Enter, Shift+Enter, PgUp і PgDn. Тоді ж у правому верхньому куті форми вводу буде показано номер поточного запису та загальну кількість записів у списку.
Для видалення непотрібного запису потрібно вибрати його у формі вводу і клацнути на кнопці Удалить.
Щоб знайти записи, що відповідають певним умовам, треба клацнути на кнопці Условие та ввести у відповідні поля потрібні умови, а для перегляду знайдених записів натискати кнопки Найти далее і Найти назад. Наприклад, якщо ввести в поле Автор символи В*, то, послідовно клацаючи на кнопці Найти далее, можна переглянути у формі лише ті записи, в яких ім'я автора починається з букви В. А щоб дізнатися, кількість яких книг у магазині перевищує 10, ввести у поле Кількість вираз >10.
Для того щоб повернутися до форми вводу, не виконуючи пошуку за умовами, слід клацнути на кнопці Правка.
Фільтрація списку
Фільтрація списку – це процес приховування непотрібних у даний момент записів та відображення записів, що задовольняють заданим умовам.
Дані, відібрані після фільтрації, можна редагувати, форматувати, друкувати, створювати на їх основі діаграми, не змінюючи порядок рядків і не переміщуючи їх.
Найпростішим методом фільтрації списку є автоматична фільтрація. Її виконують наступним чином.
1) Встановити табличний курсор в одній з комірок списку.
2) Вибрати команду Данные/Фильтр/Автофильтр, після чого праворуч від заголовків стовпців у списку з'являться кнопки зі стрілками кнопки автофільтру.
3) Клацнути на кнопці автофільтру того поля, за значеннями якого потрібно відфільтрувати список. Отримаєте список, який міститиме всі значення цього поля, а також декілька інших елементів (таких як Сортировка по возрастанию, Сортировка по убыванию, Все, Первые 10 і Условие).
4) Вибрати зі списку те значення поля, за яким слід виконати фільтрацію. На екрані залишаться тільки ті записи, що задовольняють даній умові. На малюнку показаний результат сортування за значенням Березень.
Фільтрацію списку можна також виконувати за значеннями декількох полів. У цьому випадку спочатку потрібно відфільтрувати список за значенням одного поля, а потім отриманий список відфільтрувати за значенням іншого поля.
Щоб скасувати фільтрацію та знову побачити список повністю, потрібно клацнути на кнопці автофільтру і вибрати у розкривному списку елемент Все або в меню Данные вибрати команду Фильтр/Отобразить все. А щоб видалити кнопки автофільтру, слід виконати ще раз команду Данные/Фильтр/Автофильтр.
За допомогою автофільтру можна відобразити потрібну кількість записів, що містять максимальні або мінімальні значення в певному полі. Для цього використовують елемент Первые 10 в розкривному списку автофільтру.
Якщо необхідно відобразити в списку рядки, що містять ті чи інші значення, або відповідають декільком умовам, указаним для стовпця, треба вибрати у розкривному списку автофільтра потрібного поля елемент Условие. З'явиться діалогове вікно Пользовательский автофильтр. В цьому вікні слід задати критерії, відповідно до яких потрібно виконати фільтрацію. У якості критерію можна задати:
• інтервал значень;
• значення, більші або менші, ніж задане значення;
• два різних значення;
• шаблон, відповідно до якого треба знайти записи.
На рисунку показано, які параметри слід задати в діалоговому вікні Пользовательский автофильтр, щоб відобразити в списку книга Віктора Пєлєвіна, а також тих авторів, імена яких починаються з літери Р.
Сортування списку
Сортування списку – це зміна порядку записів у списку. Зокрема, записи нашого списку книг можуть бути відсортовані за абеткою прізвищ авторів чи назв творів або за зростанням чи спаданням кількості цих книг в магазині.
Швидко відсортувати записи в списку можна за допомогою кнопок Сортировка по возрастанию і Сортировка по убываниюстандартної панелі інструментів. Для цього потрібно помістити табличний курсор на початок того стовпця в списку, значення якого треба відсортувати, і клацнути одну з названих кнопок.
Команди сортування доступні також у розкривному списку автофільтра, описаному вище.
Зазначені засоби дозволяють виконати сортування списку в межах лише одного поля, але іноді список потрібно відсортувати по декількох полях. Для цього слід скористатися командою Данные/Сортировка. Після вибору цієї команди відкриється діалогове вікно Сортировка диапазона.
В трьох розкривних списках даного вікна вибираються заголовки полів, які потрібно відсортувати, та встановлюються відповідні їм перемикачі за зростанням або за спаданням. Після того як клацнути на кнопці ОК, список буде відсортований заданим чином.
Якщо результат сортування записів не влаштовує, можна виконати команду Правка/Отменить сортировку, після чого список отримає початковий вигляд.
Проміжні підсумки
Після виконання фільтрації і сортування списку з'являється можливість обчислити проміжні підсумки для різних груп елементів списку.
Проміжні підсумки обчислюють для стовпців, що містять числа, за допомогою різних підсумкових функцій (таких як SUM або AVERAGE). Для кожної групи рядків можна визначити суму, добуток або кількість значень в деякому стовпці; середнє, максимальне чи мінімальне значення в стовпці; зсунене чи незсунене відхилення, а також зсунену чи незсунену дисперсію.
Для прикладу обчислимо проміжні підсумки в списку книг, де попередньо були відсортовані поля Дата появи в магазині та Кількість. Для цього виконаємо такі дії.
1) Встановимо табличний курсор у будь-якому місці списку.
2) Виберемо команду Данные/Итоги.
3) В отриманому діалоговому вікні Промежуточные итоги встановимо параметри, вказані на малюнку.
Зауваження. Поле, яке вибирається в першому розкривному списку, обов'язково повинно бути відсортованим.
4) Клацнемо на кнопці ОК.
Внаслідок виконання цих дій у списку автоматично з'являться формули обчислення проміжних і загальних підсумків. Загальні підсумки обчислюються на основі рядків з даними в таблиці, а не за значеннями в рядках проміжних підсумків.
У лівій частині робочого аркуша можна побачити так звану карту структури даних. Якщо клацнути на кнопці зі знакам "мінус", то відповідна група записів буде прихована, а на екрані залишитеся лише рядок з підсумковими даними для цієї групи. Щоб відобразити приховані записи, клацніть на кнопці зі знаком "плюс". Якщо клацнути на одній з кнопок із цифрами, що знаходяться в лівому верхньому куті робочого аркуша, то будуть приховані усі групи записів даного рівня.
Хід роботи
1. Внести в таблицю наступні дані з допомогою форми. Вказати формат даних:
• поле Дата надходження – тип дата;
• поле Ціна, грн. за ящ. – грошовий;
• поле Кількість - числовий, кількість знаків після коми 0.
| № | Назва товару | Дата надход-ження | Ціна, грн. за ящ | Кількість (ящ) | Сума, грн. | |
| Кава | Арабіка | 12.03.06 | 76,26 | |||
| Кава | Нескафе | 14.06.06 | 45,36 | |||
| Кава | Еліт | 12.02.06 | 50,36 | |||
| Кавовий напій | Несквік | 21.05.06 | 75,58 | |||
| Чай | Ліптон | 14.04.06 | 40,25 | |||
| Чай | Ахмат | 17.06.06 | 39,56 | |||
| Кавовий напій | Галка | 18.01.06 | 42,25 | |||
| Кавовий напій | Шипшина | 01.07.06 | 45,68 | |||
| Чай | Принцеса Нурі | 02.05.06 | 48,68 | |||
| Чай | Принцеса Канді | 05.03.06 | 36,24 | |||
| Кавовий напій | Цикорій | 04.05.06 | 45,25 | |||
| Чай | Принцеса Ява | 12.04.06 | 37,58 |
2. Використавши умовне форматування, виділити комірки, де вказана дата, до 01.04.06 червоним кольором.
3. Знайти значення полів Сума (Сума = Ціна * Кількість).
4. Використавши автофільтр, вивести на екран лише записи, які містять запис “Кава”.
5. Відсортувати список за датою.
6. Використавши фільтр користувача, вивести на екран поля, назви яких починаються на літеру “П” або на літеру “Н” (наприклад, Принцеса Ява, Нескафе).
7. Відсортувати дані по кількох полях в такій послідовності: за датою надходження, за типом (кава, чай, …), за назвою.
8. Додати в таблицю ще 15 записів (використати назви товарів, вже внесених в таблицю, внести інші значення в поля Дата та Кількість). Відсотувати отриманий список за датою. Обчислити проміжні підсумки: знайти суму для значень полів Кількість та Сума при кожній зміні в полі Дата.
Контрольні запитання
1) Що таке список в програмі Ехсеl?
2) З чого складається список?
3) Які операції можна виконувати над списком?
4) Як можна вводити інформацію в список?
5) Що таке форма вводу? Як її створити?
6) Що таке фільтрація списку?
7) Що таке авто фільтр?
8) Що таке автофильтр користувача?
9) Що таке сортування списку?
10) Як знайти проміжні підсумки в списку?
Лабораторна робота 10
Тема. Створення зведених таблиць.
Мета. Навчити створювати зведені таблиці для обчислення підсумків.
Учні повинні знати: призначення зведеної таблиці та способи її створення.
Учні повинні уміти: створювати зведені таблиці для обчислення підсумків.
Програмне забезпечення: табличний процесор Microsoft Ехсеl.
Теоретичні відомості
Зведеною називають динамічну таблицю підсумкових даних, отриманих або обчислених на основі інформації, що міститься в списках чи зовнішніх базах даних зведені таблиці дозволяють узагальнити великі обсяги даних.
Під час створення зведеної таблиці задають потрібні поля, складають макет таблиці та визначають тип виконуваних обчислень. Після формування таблиці можна змінити її структуру, щоб отримати інші підсумки за вихідними даними.
Оскільки зведена таблиця зв'язана з вихідними даними, після зміни вихідних даних її можна оновити.
Перш ніж створювати зведені таблиці, слід ознайомитись з наступними термінами.
Область Данные. Діапазон комірок зведеної таблиці, де містяться підсумкові дані. Над цими даними виконують обчислення (наприклад, операції підсумовування чи визначення середнього).
Область Столбец. Містять елементи полів, що знаходяться уздовж верхньої межі зведеної таблиці.
Область Строка. Містить елементи полів, що знаходяться уздовж лівої межі зведеної таблиці.
Область Страница. Область у лівому верхньому куті зведеної таблиці. Дозволяє зробити таблицю тривимірною. Елементи полів, поміщені в цю область, доступні у відповідному розкривному списку.
Общие итоги (Загальні підсумки). Загальні підсумки можуть бути обчислені для рядків, для стовпців, для рядків і стовпців разом або їх взагалі можна не обчислювати.
Промежуточные итоги (Проміжні підсумки). Рядки чи стовпці з проміжними підсумками зведеної таблиці.
Створення зведеної таблиці
Для створення зведених таблиць в Excel передбачений спеціальний засіб — Мастер сводных таблиц, що дозволяє за три кроки створити потрібну зведену таблицю.
Викликати майстра можна, скориставшись командою Данные/Сводная таблица. Після виконання даної команди з'явиться перше вікно майстра, де необхідно вказати джерело даних для аналізу, а також тип створюваного звіту.
Вигляд другого діалогового вікна майстра залежатиме від параметрів, заданих на першому кроці роботи майстра. Якщо вихідні дані розміщені в списку, що знаходиться на робочому аркуші Excel тобто встановлено перемикач база даних або аркуш Microsoft Office Excel), то відкриється вікно, показане на малюнку.
На другому кроці потрібно вказати діапазон комірок з вихідними даними. Якщо під час вибору команди Данные/Сводная таблица табличний курсор був встановлений в одній з комірок вихідного списку, Excel визначить потрібний діапазон автоматично.
У третьому вікні майстра зведених таблиць спочатку слід вибрати місце розташування створюваної зведеної таблиці, потім клацнути на кнопці Параметры та в отриманому діалоговому вікні встановити параметри зведеної таблиці.
Далі можна вибрати одну з наступних дій:
• клацнути на кнопці Готово, щоб переміститися на робочий аркуш і створити макет зведеної таблиці самостійно з використанням панелі інструментів Сводные таблицы;
• клацнути на кнопці Макет, щоб створити макет зведеної таблиці за допомогою майора зведених таблиць і діаграм.
Якщо клацнути на кнопці Макет, з'явиться діалогове вікно Мастер сводных таблиц и диаграмм – макет.
У правій частині зазначеного діалогового вікна будуть показані кнопки з назвами полів вихідного списку, які необхідно перетягнути в потрібні області макету таблиці (в кожну область можна перетягнути декілька полів). Макет зведеної таблиці має наступні області.
• Страница (Сторінка). Елементи полів, поміщених у дану область, стають заголовками сторінок зведеної таблиці.
• Данные (Дані). В цю область поміщають поля з даними, які слід підсумувати.
• Столбец (Стовпець). Елементи полів, поміщених у цю область, стають заголовками стовпців зведеної таблиці.
• Строка (Рядок). Елементи полів, поміщених у дану область, стають заголовками рядків зведеної таблиці.
Після того як завершено створення макету зведеної таблиці, клацніть на кнопці ОК, щоб повернутися до третього діалогового вікна майстра. Клацніть в цьому вікні на кнопці Готово, після чого зведена таблиця буде поміщена в робочий аркуш.
Приклад зведеної таблиці показано на малюнку.
В області Строка цієї таблиці знаходиться поле Товар, в області Столбец — поле Назва, а в області Данные – поле Сума, для якого підбиваються підсумки.
Модифікація зведеної таблиці
Якщо створена зведена таблиця не зовсім вдала, її структуру можна в будь-який час змінити. Для цього досить просто перетягнути мишею кнопки полів у потрібне місце. Якщо в області міститься декілька полів, можна поміняти їх місцями, змінивши таким чином порядок вкладення цих полів.
У зведеній таблиці не можливо додавати та видаляти рядки або змінювати вміст комірок.
Після створення зведеної таблиці на робочому аркуші автоматично з'являються панель інструментів Сводные таблицы, що містить інструменти настроювання зведених таблиць, а також список полів зведеної таблиці.
Крім того, відповідні команди модифікації зведених таблиць можна знайти в меню Данные та у контекстному меню зведеної таблиці.
Щоб видалити непотрібне поле, досить перетягнути мишею його кнопку за межі зведеної таблиці, після чого це поле зникне з екрану. А якщо треба додати нове поле, потрібно клацнути на його назві у вікні списку полів та перетягнути у потрібну область зведеної таблиці.
Ще один спосіб додавання поля:
1) Встановити курсор у будь-якій клітинці зведеної таблиці.
2) Вибрати команду Данные/Сводная таблица.
3) Клацнути кнопку Макет в отриманому діалоговому вікні майстра зведених таблиць.
4) Додати нове поле в потрібну область макету таблиці. Якщо вихідні дані було змінено, зведену таблицю слід оновити.
Оновлення зведеної таблиці слід виконувати вручну, оскільки автоматично Excel цього не робить.
Оновити дані в зведеній таблиці можна одним з наступних способів:
1) скористатися командою Данные/Обновить данные;
2) виконати команду Обновить данные контекстного меню зведеної таблиці;
3) клацнути на кнопці Обновить данные панелі інструментів Сводная таблица.
Параметри якого-небудь поля зведеної таблиці можна змінити, двічі клацнувши на його кнопці або вибравши команду Параметры поля з контекстного меню поля.