Зміст заняття
  1. Презентація
  2. Навички MOS 77-420
  3. Теорія:
  4. 1. Побудова простих формул
  5. 2. Використання посилань у формулах
  6. 3. Використання діапазонів даних у формулах
  7. 4. Введення у функції. Відображення дат і часу за допомогою функцій
  8. 5. Робота зі часто використовуваними функціями (Автосума)
  9. Флешкарти
  10. Практика
  11. Тест
  12. Корисне

Заняття 4 з 12

Заняття 4 Використання простих формул і функцій

Функції та формули в Excel. Типи посилань: абсолютні, відносні, змішані. Іменовані діапазони, часто вживані функції.

Презентація

Speaker Deck

SlideShare

Основи використання формул в Excel. Правила введення формул, дозволені математичні операції, порядок обчислення. Функція і формула, часто вживані функції групи Автосума.

Название навыка Номер навыка
Создание имен для диапазонов 2.3.4
Демонстрация умения пользоваться ссылками (относительными, абсолютными и смешанными) 4.1.1
Определение порядка вычисления 4.1.2
Ссылки на диапазоны данных в формулах 4.1.3
Демонстрация работы функции СУММА (SUM) 4.2.1
Демонстрация работы функций МАКС (MAX) и МИН (MIN) 4.2.2
Демонстрация работы функций СЧЕТ (COUNT) 4.2.3
Демонстрация работы функции СРЕДНЕЕ (AVERAGE) 4.2.4

Теорія:

1. Побудова простих формул

Відеоверсія

Відео · YouTube

Текстова версія

Саме можливість виконувати різноманітні обчислення принесла світову популярність табличному процесору Excel із пакету Microsoft Office.

До цього заняття ми фактично не використовували Excel для проведення розрахунків, починаючи з цього заняття спробуємо, якщо не повністю розкрити, то продемонструвати той потенціал, що закладено в цю програму.

Основи побудови формул

Якщо на початку клітинки поставити знак "=", то Excel починає сприймати клітинку як формулу.

В якості найпростішого прикладу формули можна показати просту операцію додавання двох чисел, віднімання, множення та ділення. В Excel працюють стандартні математичні операції і послідовність їх застосування.

Найпростіша формула в Excel
Найпростіша формула в Excel

За замовчуванням, у клітинці, що містить формулу, для користувача відображається результат її обчислення; якщо потрібно подивитися самі формули, це можна зробити командою відображення формул "Показати формули", група "Залежності формул", вкладка "Формули", або натиснути поєднання клавіш Ctrl+` (клавіша тильда знаходиться на початку цифрового ряду клавіатури).

Гаряче поєднання

Ctrl+` перемикає режими відображення даних/формул в Excel

Формула є рівнянням, яке виконує обчислення, такі як: додавання, віднімання, множення, ділення. В Excel значеннями формули можуть бути число, адреса клітинки, дата, текст, булеве значення (істина або хиба). Найчастіше це або число, або адреса клітинки.

Формули містять два типи елементів: операнди та оператори обчислення. Операнди — це значення, що використовуються в обчисленнях. Операнд може бути константою, наприклад число "5", або змінною, тобто посиланням на клітинку, діапазон або іншу формулу, наприклад посилання на клітинку, яка містить число "5".

Константа — це значення, яке вводиться безпосередньо у формулу (число, текст, дата).

Змінна — це символьне позначення значення, яке знаходиться поза формулою.

Використання посилання та константи у формулі Excel
Використання посилання та константи у формулі Excel

Оператор обчислення визначає саме обчислення; щоб Excel почав виконувати обчислення у формулі, на початку потрібно поставити знак "=". Excel підтримує роботу з такими типами арифметичних операторів:

Оператор Результат
+ Додавання
- Віднімання
* Множення
/ Ділення
% Відсоток (сота частина числа)
^ Піднесення до степеня

Під час введення формули оператори і значення відображаються і в клітинці, і в самій формулі; коли введення завершено, у рядку формул буде формула, тоді як у клітинці — її результат.

Порядок обчислення формули

Коли у формулі застосовується кілька обчислювальних операторів, то порядок їх обчислення підпорядковується загальним математичним правилам. Наступний порядок застосовується при обчисленні формули (записано в порядку спадання пріоритету обчислення):

  1. Негативні значення (-);
  2. Відсотки (%);
  3. Піднесення до степеня (^);
  4. Множення (*) і ділення (/);
  5. Сума (+) і різниця (-).

Якщо поряд стоять два або більше обчислювальних операторів одного рівня, то порядок їх обчислення йде зліва направо. Наприклад, "=5+5+3-2"; хоча тут цей порядок має значення радше для логіки виконання обчислення, бо віднімете ви число "2" в кінці чи на початку — на результат це не вплине. Якщо виникає необхідність підвищити пріоритет обчислення окремої частини формули, то, як і в математиці, цю частину потрібно взяти в круглі дужки.

Кілька прикладів роботи обчислень в Excel:

Приклади простих формул в Excel
Приклади простих формул в Excel

Гаряче поєднання

Якщо під час введення формули ви передумали, просто натисніть "Esc" і значення в клітинці не зміниться. Якщо формулу вже змінили (завершили введення за допомогою Enter), то повернути попереднє значення можна командою "Скасувати" на панелі швидкого доступу або поєднанням Ctrl+Z.

Щоб відредагувати існуючу формулу, потрібно встановити курсор у клітинку з формулою і натиснути клавішу "F2", або клікнути у рядку формул, або двічі клікнути по цільовій клітинці.

2. Використання посилань у формулах

Відеоверсія

Відео · YouTube

Текстова версія

Раніше згадувалося, що одним із значень, які використовуються у формулах, є змінні. Змінні використовуються набагато частіше, ніж константи; більш того, рекомендовано всі константи заміняти змінними (докладніше про це поговоримо в розрізі розгляду абсолютних посилань).

Отже, змінні в Excel представлені посиланнями на інші клітинки. Розглянемо два приклади, які фактично ідентичні.

Посилання на клітинки у формулі Excel
Посилання на клітинки у формулі Excel

Кожна клітинка знаходиться на перетині рядків і стовпців і має відповідне позначення:

  • "E2" — стовпець "E", другий рядок;
  • "F2" — стовпець "F", другий рядок;
  • "G2" — стовпець "F", другий рядок.

Перевага такого запису в тому, що при зміні значень у клітинках, задіяних у формулі, результат обчислення також зміниться. Звісно, формули можуть містити і посилання (змінні), і числа (константи) одночасно.

Відносні посилання

У наведеному прикладі розглянуто найпростіший тип посилань — відносні посилання; принцип його роботи легко зрозуміти на прикладі перерахунку денного заробітку в іншу валюту.

Відносне посилання в Excel
Відносне посилання в Excel

Проста формула перерахунку актуального заробітку в рублі вводиться в клітинку C2, а потім за допомогою маркера автозаповнення розтягується на інші дні; як можна помітити, для клітинки C3 у формулі використовується заробіток за 02.03.2016, хоча формула вводилася "=B2*71", тобто при зсуві на одну клітинку вниз введене відносне посилання також змінює свою адресу. Те саме відбувається, якщо розтягнути маркер автозаповнення в будь-який бік (вліво, вправо, вгору чи вниз) — відносне посилання завжди буде знаходитися на однаковій відстані від клітинки з формулою, де воно використовується. У даному випадку це на одну клітинку вліво, тобто якщо потягнути маркер автозаповнення вправо, то в формулі буде використовуватися стовпець "C" (і число в залежності від поточного рядка).

Як вводити посилання у формули Excel

Посилання у формули Excel можна вводити безпосередньо з клавіатури (тільки не забути вказувати назви стовпців в англійській розкладці), але більш розповсюдженим способом є клік лівою кнопкою миші по тій клітинці, яка має використовуватися у формулі. Наприклад, у прикладі вище можна підсумувати загальний заробіток просто клікаючи по потрібних клітинках та додаючи їх.

Введення посилань у формулу Excel
Введення посилань у формулу Excel

Для кращого візуального сприйняття посилань у формулі клітинки підсвічуються різними кольорами.

Абсолютные ссылки

У прикладі перерахунку заробітку з доларів у рублі ми використовували курс перерахунку як константу, тобто вводили число безпосередньо в саму формулу. Якщо курс буде змінюватися (а він буде змінюватися), то доведеться переробляти формулу і не забути скористатися автозаповненням для множення її на інші клітинки, що досить незручно.

Можна записати курс як змінну посиланням на клітинку, але тоді при автозаповненні на інші клітинки відносне посилання зіб'ється, тобто буде посилатися на неправильні клітинки. Для таких випадків у Excel передбачені абсолютні посилання.

Абсолютне посилання – це посилання на клітинку, адреса якої не змінюється при автозаповненні діапазонів. Щоб зробити посилання абсолютним, потрібно в позначенні посилання перед ідентифікатором стовпця і рядка поставити по знаку долара.

Приклад абсолютного та відносного посилання
Приклад абсолютного та відносного посилання

Знак долара можна ввести з клавіатури, але швидше буде натиснути клавішу "F4" на відносному посиланнi, тобто на посиланнi, на якому встановлено курсор. Повторне натискання клавіші "F4" буде прибирати/додавати знак $ біля рядка/стовпця. З’являтимуться проміжні значення: посилання з одним знаком долара — це змішані посилання, їх розгляд буде трохи пізніше.

На замітку

Абсолютне посилання — це посилання з двома знаками долара.

Тепер, коли курс у формулі зафіксовано абсолютним посиланням, при його зміні достатньо один раз змінити клітинку F1 і всі формули, що використовують значення цієї клітинки, будуть перераховані автоматично, а при розтягуванні формули в будь-який бік формула завжди буде посилатися на ту саму конкретну клітинку.

На початку розгляду цього питання ми згадували, що в формулах використання констант краще звести до мінімуму. Найкращим рішенням буде замінити їх абсолютними посиланнями, які, до того ж, можна ще й підписати.

Змішані посилання

При автозаповненні відносне посилання буде змінюватися завжди, незалежно від напрямку автозаповнення, абсолютне ніколи не буде змінюватися. Як бути, якщо потрібно, щоб при автозаповненні формули вниз або вгору посилання змінювалося, а при русі вліво або вправо — ні, або навпаки, при розтягуванні вліво або вправо змінювалося, а при русі вгору або вниз залишалося незмінним?

У такому випадку слід використовувати змішані посилання. Змішане посилання — це посилання, яке по одному з напрямків (рядку або стовпцю) є абсолютним, а по іншому — відносним. Яка частина посилання поводиться як абсолютна, визначається знаком долара.

Наприклад, у розглянутому прикладі перетворення денного заробітку абсолютне посилання курсу рубля до долара можна замінити на F$1 і нічого не зміниться, тому що рядок ми зафіксували, а стовпець і так не змінювався. Щоб зробити більш наочну презентацію, можна розширити таблицю перерахунками в додаткові валюти.

Змішані посилання в Excel
Змішані посилання в Excel

Тут використовується два види змішаних посилань: перше посилається на суму заробітку в доларах і зафіксоване по стовпцях, що дозволяє, розтягуючи формулу вниз, використовувати для кожного дня свій заробіток; а друге посилається на курс, зафіксоване по рядках, що дозволяє змінювати курс при автозаповненні формули вліво або вправо. Тут важливо розуміти, що для перерахунку заробітку в три валюти формула була введена всього ОДИН! раз у клітинку C4. Усі інші клітинки були заповнені за допомогою автозаповнення.

Важливо

Розуміння принципів роботи різних типів посилань є основою роботи в табличному процесорі. Не вивчивши докладно це питання, будь-яке подальше опанування матеріалу втрачає сенс. ЦЕ ОСНОВА ОСНОВ!

Які ще бувають типи посилань

Усі посилання, які ми розглядали, посилалися на клітинки того самого аркуша, де була розташована формула, проте користувач може робити посилання на клітинки іншого аркуша. При цьому всі правила, розглянуті раніше про відносні, абсолютні та змішані посилання, працюють також: наприклад, можна винести курси валют на інший аркуш — єдине, що зміниться, це те, що перед посиланням буде ім'я аркуша, яке береться в одинарні лапки і відокремлюється від самої посилання знаком оклику.

Посилання на інші листи книги
Посилання на інші листи книги

Взагалі, якщо працювати з посиланнями на одному аркуші, то ім'я аркуша також можна не писати — усе працюватиме; просто якщо формула і посилання знаходяться на одному аркуші, створена простіша запис.

Це ще не все: можна посилатися і на клітинки, які знаходяться в інших книгах — це вже зовнішні посилання; у цьому випадку в записі посилання фігуруватиме й ім'я книги.

Зовнішні посилання — посилання на клітинки з іншої книги
Зовнішні посилання — посилання на клітинки з іншої книги

Працюючи зі зовнішніми посиланнями, важливо розуміти, що якщо книгу, на яку налаштовані посилання, перемістити або видалити, то обчислення будуть порушені.

Зовнішнє посилання з вказанням шляху до книги
Зовнішнє посилання з вказанням шляху до книги

Шлях до книги з посиланнями відображатиметься у формулі після того, як книгу буде збережено, і, якщо вона закрита, як тільки ми відкриємо книгу, у формулі знову відображатиметься тільки ім'я книги в квадратних дужках.

У підсумку: посилання бувають відносними, абсолютними або змішаними, при цьому вони можуть розміщуватися на тому самому аркуші з формулою, на різних аркушах однієї книги або в різних книгах.

3. Використання діапазонів даних у формулах

Відеоверсія

Відео · YouTube

Текстова версія

У Excel група клітинок називається діапазоном. Групи клітинок можуть бути суміжними або можуть знаходитися на відстані одна від одної. Ви можете присвоювати імена групам клітинок, змінювати межі діапазонів після визначення їх імені та використовувати імена у формулах замість простих безіменних посилань на клітинки.

Іменування діапазонів даних

Якщо ви часто працюєте з певним діапазоном, дати йому ім'я буває досить зручно, щоб надалі оперувати не безіменним діапазоном/клітинкою: "C3:C15", а зрозумілим "прибуток_2015" тощо.

Для іменування і навігації по іменованих діапазонах і клітинках використовується група "Визначені імена" вкладки "Формули", а також віконце "Ім'я" (Name box), яке ми почали розглядати у другому питанні другого заняття.

Іменування діапазону в Excel
Іменування діапазону в Excel

Найпростіший спосіб іменування діапазону за допомогою віконця "Ім'я" присвоїть ім'я діапазону з параметрами за замовчуванням, тобто іменований діапазон буде діяти на всіх аркушах книги; якщо давати ім'я за допомогою команди "Призначити ім'я", то в появленому діалоговому вікні можна налаштувати додаткові параметри, найважливішим з яких є область дії іменованого діапазону.

Діалогове вікно створення нового іменованого діапазону
Діалогове вікно створення нового іменованого діапазону

За замовчуванням область дії визначена на рівні книги, тобто якщо ви створюєте на одному з аркушів книги іменований діапазон, то його можна буде використовувати на будь-якому аркуші книги, а також не можна створити другий іменований діапазон з таким самим ім'ям. Користувач може змінити область дії іменованого діапазону під час його створення, обравши один із наявних аркушів у книзі. Цей аркуш не обов'язково має збігатися з аркушем, на якому розташований сам діапазон; у такому випадку можна створити кілька діапазонів з однаковими іменами, але кожен із них діятиме на «своєму» аркуші.

Ще один спосіб швидкого створення іменованого діапазону — це використання команди "Створити з виділеного" у тій самій групі "Визначені імена". Суть полягає в тому, що виділяється діапазон разом із заголовком, який і буде носити ім'я діапазону. Excel, до речі, сам визначає, яку клітинку використати під ім'я діапазону.

Створення імені з виділеного діапазону
Створення імені з виділеного діапазону

Виділення стовпця "У євро" і використання команди "Створити з виділеного" рівнозначне виділенню діапазону E4:E13 і присвоєнню йому імені "В_евро".

Переглянути всі іменовані діапазони, видалити непотрібні, змінити якогось діапазону його діапазон можна в Диспетчері імен, який викликається однойменною командою з групи "Визначені імена".

Диспетчер імен
Диспетчер імен

Як використовувати іменовані діапазони

Іменовані діапазони використовуються у формулах нарівні зі стандартними діапазонами, але є деякі особливості. По-перше, іменований діапазон ЗАВЖДИ є абсолютним посиланням; по-друге, у формулі достатньо почати вводити ім'я іменованого діапазону, і Excel його запропонує; якщо є кілька схожих ("заробіток_долари", "заробіток_гривні" тощо), то можна буде мишкою вибрати потрібний.

Іменований діапазон у формулу можна вставити за допомогою команди "Використати в формулі" групи "Визначені імена".

Іменовані діапазони ідеально підходять для використання разом із функціями, що працюють з діапазонами; детальніше до розгляду функцій ми підемо трохи пізніше, а почнемо вже зараз.

Наприклад, у прикладі розрахунку та переведення в різні валюти добового заробітку ми підраховували загальну суму в доларах простим додаванням денних доходів. У реальній роботі Microsoft Excel так робити неправильно, оскільки є функції, що автоматизують ці операції. Підрахуємо суму доходів у різних валютах, використовуючи посилання на діапазони, а потім зробимо те саме, тільки в якості аргументів введемо імена діапазонів.

Приклад використання іменованого діапазону
Приклад використання іменованого діапазону

Якщо не використовувати імена клітинок, то можна підрахувати суму заробітку в одній валюті, а для інших заповнити за допомогою автозаповнення. З іменованими діапазонами такий «фокус» не пройде, оскільки діапазону присвоюється абсолютне посилання, проте, якщо потрібно буде просумувати діапазон, який знаходиться за межами видимої області, то використання імені замість посилань буде бажанішим.

Якщо в книзі створено багато іменованих діапазонів або ви просто не пам’ятаєте ім’я потрібного діапазону клітинок, на допомогу прийде діалогове вікно "Вставка імені", яке зручно використовувати під час складання формули і яке викликається за допомогою команди "Використовувати в формулі", група "Визначені імена".

Вставка імені
Вставка імені

На завершення розгляду теми іменування діапазонів даних можна вказати правила для створення імен діапазонів:

  1. Довжина імені не більше 255 символів.
  2. Ім’я діапазону може починатися або з літери, або з символів "_" та "\\". У самому імені можна використовувати цифри, літери, знак підкреслення. Наприклад, "\\імя_ячейки" або "_имя\\ячейки.1" – допустимі назви, а "1имя_ячейки" – не можна, оскільки починається з цифри.
  3. Ім’я не може складатися з поодиноких букв: "R", "r", "C","c", адже їхній ввід у вікно "Ім’я" призводить до виділення рядка (row) або стовпця (column).
  4. В іменах не можна використовувати пробіли, Microsoft радить замінювати їх символом підкреслення "_" або крапкою ".". Наприклад, "доходи_октябрь", "прибыль.год".
  5. Ім’я діапазону не може збігатися з ім’ям посилання, наприклад, "A5" або "$B$5".

4. Введення у функції. Відображення дат і часу за допомогою функцій

Відеоверсія

Відео · YouTube

Текстова версія

Деякі обчислення користувачі виконують частіше за інші, деякі обчислення є обов’язковими для певної галузі. Щоб щоразу не вводити формулу вручну, у Microsoft Excel розробили функції. Функція фактично є попередньо визначеною формулою, наприклад, формулою суми, яку ми раніше вже трохи розглядали.

В Excel передбачено велику кількість функцій — від досить простих, наприклад, середнє, підрахунок кількості клітинок, мінімум, сума тощо, до складних статистичних або фінансових обчислень, наприклад, дисперсія, середньоквадратичне відхилення тощо.

Зручний довідник із роботи з функціями представлено на нашому сайті: //msoffice-prowork.com/spravka-ms-excel/

Введення у функції

На замітку

Назви функцій нечутливі до регістру, проте в Excel їх прийнято записувати великими літерами для зручності читання. Проте користувачу писати великими немає потреби, бо Excel автоматично перетворює малі літери в імені функції на прописні.

Викликати функцію для вставки в формулу можна кількома способами:

  1. На вкладці "Формули" в групі "Бібліотека функцій" скористатися командою "Вставити функцію" або вибрати необхідну з відповідної категорії.

  2. Викликати команду "Вставити функцію", яка знаходиться ліворуч біля рядка формул.
  3. Поставити знак "=" у рядок формул і вибрати одну з останніх 10 використаних функцій у вікні "Ім’я" або там же викликати команду вставки будь-якої функції.
  4. Скористатися гарячим поєднанням клавіш "Shift+F3".
  5. Поставити знак "=" у рядок формул і почати вводити функцію, Excel почне пропонувати ймовірні варіанти функцій.
  6. Різні способи додавання функції
    Різні способи додавання функції
  7. Якщо ви точно не пам’ятаєте ім’я функції, але пам’ятаєте, що вона має робити, то можна ввести бажану дію у вікно "Що ви хочете зробити" (Tell me), наприклад, ввівши "середнє", Excel запропонує скористатися функцією СРЗНАЧ, яка обчислює середнє арифметичне.
  8. Використання вікна Tell me для вставки функції (Excel 2016)
    Використання вікна Tell me для вставки функції (Excel 2016)
  9. На вкладці "Основне" в групі редагування є команда "Автосума", яка пропонує часто використовувані функції (середнє, сума, максимум, мінімум, лічильник), але в спадному меню можна викликати діалогове вікно вставки будь-якої функції Excel.
  10. Часто використовувані функції в групі Автосума
    Часто використовувані функції в групі Автосума

Як ви вже помітили, функції суми, середнього, максимуму і мінімуму використовуються частіше за інші, тому вони навіть винесені в окрему групу. Насправді дуже швидко дізнатися ці величини можна навіть без введення формул. На рядку стану користувач має можливість вивести швидкий підрахунок середнього, кількості, кількості чисел, максимуму і мінімуму (галочками відмічені підрахунки, що виведені на панель стану в даний момент).

Виведення інформації на панель стану
Виведення інформації на панель стану

Для суми існує гаряче поєднання клавіш "Alt+=", можна просто ввести його поряд із діапазоном із даними, і Excel виділить найбільш підходящий (на думку програми) діапазон, який можна скоригувати, або можна виділити діапазон і натиснути гаряче поєднання — Excel залежно від орієнтації діапазону (горизонтальний чи вертикальний) помістить суму у клітинку під або над діапазоном.

Підрахунок суми діапазону за допомогою поєднання Alt+=
Підрахунок суми діапазону за допомогою поєднання Alt+=

Функції дати і часу

У Excel дати фактично є числовими значеннями, але відформатовані так, щоб виглядати як дата. Те саме стосується і часу. Це твердження легко перевірити, просто ввівши будь-яке додатне число і відформатувавши його відповідним чином. Саме з цієї причини можливі арифметичні операції з датами, наприклад, можна обчислити кількість днів між двома датами, просто віднявши одну дату з іншої.

Відправною точкою для дат є 1 січня 1900 року, тобто саме ця дата відповідатиме числу "1", якщо встановити відповідне форматування, "2" = 2 січня 1900 року і т.д. Дати можна додавати, віднімати, множити, ділити так само, як і числа.

У Microsoft Excel за замовчуванням усі клітинки мають числовий формат "Загальний", це означає, що табличний процесор намагатиметься підлаштувати форматування залежно від введених даних.

Наприклад, ввівши "15.03.2016" числовий формат буде встановлено на "Дата", якщо потім просто видалити дату за допомогою клавіші Del (рівнозначно очищенню вмісту) і ввести число, наприклад, 5, то в клітинці відобразиться дата: 05.01.1900. Тому потрібно або повністю очищати клітинку (разом із форматами), або після введення даних змінити числовий формат клітинки.

Функція СЕГОДНЯ (TODAY)

Функція СЕГОДНЯ () або TODAY () в англомовній версії Excel проста в розумінні і її синтаксис предельно простий, оскільки аргументи взагалі відсутні. Ця функція повертає поточну дату. Під час відкриття книги завжди буде відображена актуальна дата, тому якщо потрібно зафіксувати конкретну дату, її доведеться ввести вручну.

Ввід будь-якої формули повинен починатися зі знака "=", якщо формула складається з однієї функції, то перед нею слід поставити "=", якщо функція додається в середині формули, повторно рівно ставити не можна. У формулі може бути тільки один знак "=".

Функція СЕГОДНЯ

Саме по собі використання функції СЕГОДНЯ не виглядає надто цікавим, але потрібно розуміти, що при побудові формул ми можемо використовувати кілька функцій. Наприклад, можна використати СЕГОДНЯ при обчисленні свого віку, у цьому випадку формула набуде вигляду:

Обчислення власного віку в Excel
Обчислення власного віку в Excel

Функція ТДАТА (NOW)

Функція ТДАТА () або NOW () в англомовній версії Excel дуже схожа на попередню з тією лише відмінністю, що повертає не тільки поточну дату, а й точний час. Час у Excel представлено дробовим числом.

Виведення поточної дати в Excel
Виведення поточної дати в Excel

Оновлення поточного часу відбувається щоразу при відкритті файлу або при перерахунку аркуша.

5. Робота зі часто використовуваними функціями (Автосума)

Відеоверсія

Відео · YouTube

Текстова версія

В Excel багато функцій: деякі з них використовуються рідко, деякі будуть цікаві лише певній категорії працівників, а є функції, які завжди і скрізь будуть корисні, незалежно від вашої спеціальності та виду розрахунків.

У Microsoft це теж розуміють, тому зібрали такі функції не просто в окремий набір, який називається "Автосума", але й розмістили його в різних місцях інтерфейсу програми Excel.

У «Автосумі» присутні функції: СУММ (SUM), СРЗНАЧ (AVERAGE), СЧЁТ (COUNT) (підрахунок кількості клітинок у діапазоні з числами), МАКС (MAX) та МИН (MIN).

Групу функцій «Автосума» можна знайти на вкладці «Основне» у групі «Редагування».

Часто використовувані функції в групі Автосума
Часто використовувані функції в групі Автосума

Група функцій «Автосума» представлена на вкладці «Формули» в групі «Бібліотека функцій».

Функція СУММ (SUM)

Використання функції СУММ — одне з перших, що має опанувати користувач, чия робота пов’язана з Excel; ця функція просто сумує клітинки діапазону або кількох суміжних діапазонів.

У другому питанні цього заняття ми шукали загальну суму місячного заробітку простим введенням посилань на клітинки зі знаком «+», тоді це робилося в рамках пояснення роботи з посиланнями в Excel; у реальній роботі так робити неправильно, оскільки є функція «СУММ».

Демонстрація роботи функції СУММ
Демонстрація роботи функції СУММ

Якщо ви назвали діапазон, то в аргументи функції можна просто ввести його ім’я — це зручно, якщо діапазони розкидані по аркушах книги Excel.

Функція СРЗНАЧ (AVERAGE)

Функція СРЗНАЧ підраховує арифметичне середнє діапазону; у нашому випадку суму за 10 періодів (днів) потрібно поділити на 10. Як і в попередньому випадку, в аргумент можна ввести поіменований діапазон.

Обчислення середнього в Excel
Обчислення середнього в Excel

До речі, варто зазначити, що в нашому конкретному прикладі використання поіменованих діапазонів не дуже зручне, оскільки це виключає можливість застосування автозаповнення. Потрібно або видалити імена діапазонів, або вручну ввести поіменований діапазон відносним посиланням (тобто не виділяти його мишкою, а ввести букви й символи діапазону в функцію з клавіатури).

Якщо видаляти ім’я діапазону не можна, можна трохи спростити процес запису поіменованого діапазону посиланнями. Виділяємо на одну клітинку більше поіменованого діапазону, а потім у рядку формул з клавіатури виправляємо виділене.

Функція СЧЁТ (COUNT), СЧЁТЗ (COUNTA)

Функція СЧЁТЗ (COUNTA) не представлена в швидкодоступній групі «Автосума», але вона використовується, можливо, навіть частіше, ніж СЧЁТ, тому має сенс її розглянути.

Отже, функції СЧЁТ (COUNT) і СЧЁТЗ (COUNTA) рахують кількість заповнених клітинок у діапазоні; проте перша рахує лише клітинки з числами (причому в усіх числових форматах: число, фінансовий, дата, час), тоді як друга підрахує всі непусті клітинки. Для прикладу використаємо діапазон із текстовим заголовком — функція СЧЁТЗ має показати на одне значення більше, ніж СЧЁТ.

Підрахунок клітинок з даними та з числовими даними
Підрахунок клітинок з даними та з числовими даними

Функції МАКС (MAX) і MIN (MIN)

Функції МАКС (MAX) і MIN (MIN) шукають максимальне та мінімальне значення в діапазоні; синтаксис використання не відрізняється від розглянутих вище варіантів.

Пошук максимального і мінімального значень в Excel
Пошук максимального і мінімального значень в Excel

Для кращого візуального сприйняття відформатуємо обчислювані клітинки одним із передвстановлених стилів клітинки.

Флешкарти

Так звані карти пам’яті: дивитеся на карту й намагаєтеся відповісти; за кліком на карту відобразиться правильна відповідь. Карти пам’яті корисні для запам’ятовування ключових положень заняття. Усі заняття цього курсу забезпечені картами пам’яті.

Практика

У практичній частині ви знайдете завдання за пройденим заняттям. Після їх виконання у вас є можливість порівняти свій варіант із підготовленою лектором відповіддю. Наполегливо рекомендується дивитися розв'язок тільки після того, як ви виконали завдання самостійно. Для деяких завдань є невеликі підказки.


Завдання 1. Обчислення антропометричних даних людини

На місці злочину залишився слід, імовірно злочинця, 43-й розмір, чоловіче черевик. На підставі цієї інформації, а також зважаючи на той факт, що свідки відзначили, як хлопець близько 27 років покидав місце злочину, потрібно розрахувати деякі антропометричні дані людини: зріст, довжину кроку, ширину ступні, довжину п'ятки, довжину голови, плеча, передпліччя, стегна, гомілки та ідеальну масу.

Формули для розрахунку показників:

  • Зріст (см) = (довжина ступні – 15) *100/15,8+100
  • Довжина кроку (см) = довжина ступні * 3
  • Ширина ступні (см) = Зріст / 18
  • Довжина п'ятки (см) = Зріст / 27
  • Голова (см) = Зріст / 8
  • Плече (см) = (Зріст – 73,6) / 2,97
  • Передпліччя (см) = (Зріст – 80,4) / 3,65
  • Стегно (см) = (Зріст – 69,1) / 2,24
  • Гомілка (см) = (Зріст – 72,6) / 2,53
  • Ідеальна маса (кг) = (Зріст * 3 / 10 + 450 + Вік) * 0,25 - 55

Інструкції щодо виконання обчислень:

  1. Відкрити книгу "Заняття 4.1 старт".
  2. Знайти відповідність довжини ступні розміру взуття в інтернеті.
  3. Розрахувати показники на основі знайденого значення довжини ступні.
  4. У формулах використовувати посилання на попередньо розраховані значення; наприклад, якщо для розрахунку плеча використовується зріст, то слід використовувати посилання на розраховане значення зросту.
  5. Зберегти книгу під іменем "Заняття 4.1 виконано".
Переглянути розв'язок
Відео · YouTube


Завдання 2. Використання абсолютного посилання в розрахунках

Розрахунок антропометричних показників ще для двох розмірів взуття. Потрібно переробити введені формули так, щоб для двох нових розмірів взуття (довжина ступні 29 та 31 см) розрахунки виконувалися автоматично після автозаповнення клітинок праворуч.

  1. Відкрити книгу з попереднього завдання "Завдання 4.1 виконано".
  2. Показати приховані стовпці C і D.
  3. У формулах, де використовується вік імовірного злочинця, змінити відносне посилання на абсолютне.
  4. Виділити діапазон клітинок B4:B13 і за допомогою автозаповнення виконати розрахунки для двох сусідніх стовпців для довжини ступні 29 та 31.
  5. Зберегти книгу під іменем "Завдання 4.2 виконано".
Переглянути розв'язок
Відео · YouTube


Завдання 3. Використання змішаних посилань

Перевести зароблені гроші в інші валюти.

  1. Відкрити книгу "Завдання 4.3 старт".
  2. На підставі курсу рубля до долара зробити розрахунок діапазону C2:C11. При введенні посилання на курс рубля використати змішаний тип посилання (абсолютний по рядках і відносний по стовпцях). При введенні посилання на денний заробіток використати змішаний тип посилань (абсолютний по стовпцях і змішаний по рядках).
  3. За допомогою автозаповнення розтягнути діапазон на два стовпці праворуч для розрахунку заробітку в гривнях і євро.
  4. Зберегти книгу під іменем "Завдання 4.3 виконано".
Переглянути розв'язок
Відео · YouTube


Завдання 4. Робота з іменованими діапазонами і функціями

  1. Відкрити книгу з попереднього завдання "Завдання 4.3 виконано".
  2. Обчислити показники суми, середнього, максимального і мінімального значень для стовпців "В рублях", "В гривнах" і "В евро".
  3. Діапазону заробітку в доларах присвоїти ім’я "заработок" (B2:B11).
  4. Обчислити показники суми, середнього, максимального і мінімального значень для стовпця заробітку в доларах, але в якості аргумента функцій вказувати ім’я діапазону. Наприклад, для суми: =СУММ(заработок) (СУММ (SUM)).
  5. За допомогою функцій СЧЁТ (COUNT) і СЧЁТЗ (COUNTA) визначити кількість дат у діапазоні A1:A11 та загальну кількість записів у цьому ж діапазоні.
  6. У клітинку J2 за допомогою функції СЕГОДНЯ (TODAY) ввести актуальну дату.
  7. Зберегти книгу під іменем "Завдання 4.4 виконано".
Переглянути розв'язок
Відео · YouTube

Тест

Корисне