Використання функцій

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

Дана операція дуже часто зустрічається в бухгалтерських бланках і фінансових розрахунках У Excel існує кілька варіантів використання цієї функції Можна просто перерахувати конкретні осередки, написавши, наприклад, таку формулу = A1 + A2 + A3 + A4, або написати формулу в такому вигляді = СУММ (A1 A2 A3 A4) У цьому випадку підсумовуються значення чотирьох осередків, але використовуються тільки ці конкретні осередки Осередки розташовані поруч і складають стовпець деякої таблиці, однак при додаванні рядка між ними нічого не відбувається,

підсумовуються і раніше тільки чотири осередки, хоча адреси осередків автоматично змінюються Можна використовувати вказівку діапазону комірок У цьому випадку формула буде записана у вигляді = СУММ (A1: A4) Тут як і раніше використовуються чотири осередки У цьому випадку при додаванні рядка між осередками відбувається автоматичне зміна діапазону, і підсумовування відбуватиметься за формулою

= СУММ (A1: A5), тобто в операції братимуть участь вже пять осередків Цю корисну можливість по-різному вказувати адреси комірок можна використовувати при створенні певних бухгалтерських бланків

У Excel є зручний механізм вставки функції підсумовування Якщо виділити деякий діапазон комірок стовпчика або рядка і натиснути кнопку Σ (сума) у групі Редагування на вкладці Головна, то в наступну комірку автоматично вводиться виклик функції підсумовування із зазначенням даного діапазону комірок

Існує також більш потужний механізм автоматизації подібних обчислень ний – майстер підсумовування Він реалізований у вигляді надбудови За замовчуванням ця надбудова не встановлена, і тому майстер недоступний Після подключе ня надбудови даний майстер зявляється в групі Рішення вкладки Формули у вигляді кнопки Часткова сума Детальніше про надбудовах і їх підключенні буде розказано далі З назви майстра ясно, що він дозволяє створити виклик функції підсумовування діапазону комірок, що враховує деякі умови На рис 210 показаний приклад застосування майстра підсумовування В осередках A1: A10 знаходяться числа від 1 до 10, в клітинку A15 поміщена сформована майстром формула {= СУММ (ЕСЛИ ($ A $ 2: $ A $ 10> 5 $ A $ 2: $ A $ 10 0))} Умова полягає в тому, що для підсумовування використовуються тільки значення, великі 5

Цією ж мети можна досягти іншим способом Для часткового підсумовування вмісту осередків призначене декілька функцій Ці функції дозволяють підсумувати комірки деякого діапазону з урахуванням додаткових умов Наприклад, в операції підсумовування, як і при використанні майстра, не братимуть участі осередку, в яких знаходяться числа, менші заданого У даному прикладі можна застосувати функцію СУММЕСЛІ, яка передбачає використання одного критерію Осередок A13 містить формулу = СУММЕСЛІ (A1: A10 > 5; A1: A10) Як видно з її тексту, критерій підсумовування використовується той же самий, але вид формули іншою Для більш складних випадків одночасного використання декількох критеріїв призначена функція СУММЕСЛІМН

У багатьох випадках замість функції СУММ можна застосовувати функцію ПРОМЕЖУТОЧНИЕІТОГІ (Номер_функції посилання1 посилання2 ..) Номер_функції – число від 1 до 11 (з включенням значень прихованих осередків) або від 101 до 111 (з виключенням прихованих значень), яке вказує, яку функцію використовувати при обчисленні підсумків усередині списку В якості функцій використовуються обчислення середнього значення, кількості значень, твір, сума і т д

На рис 211 показаний приклад застосування такої функції Осередки D1: D8 містять числові значення, причому в комірці D4 знаходиться функція = ПРОМЕЖУТОЧНИЕІТОГІ (9 D1: D3), а в комірці D9 – формула = ПРОМІЖНІ ПІДСУМКИ (9 D1: D8) Другий рядок прихована У цьому випадку значення першого аргументу функції задає виконання підсумовування Таким чином, в комірці D4

обчислюється проміжне значення суми У сусідній комірці E9 знаходиться формула звичайного підсумовування всіх осередків з використанням функції СУММ Як видно на цьому прикладі, при використанні функції ПРОМЕЖУТОЧНИЕІТОГІ значення комірки D4 не бере участі в загальному підсумовуванні В осередку G4 знаходиться формула = ПРОМЕЖУТОЧНИЕІТОГІ (109 G1: G3), комірці G9 – відповідно формула = ПРОМЕЖУТОЧНИЕІТОГІ (109 G1: G8) Відмінність формул полягає в різних значеннях першого аргументу У другому випадку значення прихованих осередків не враховується

Рис 210 Приклад часткового підсумовування

В осередках J9 і K9 знаходяться формули = ПРОМЕЖУТОЧНИЕІТОГІ (2 D1: D8) і = ПРОМЕЖУТОЧНИЕІТОГІ (102 D1: D8) За цими формулами обчислюється кількість значень у зазначених діапазонах осередків

Рис 211 Приклад використання функції ПРОМЕЖУТОЧНИЕІТОГІ

Пошук даних в клітинках аркуша Excel – не така очевидна операція, як підсумовування даних Не всі користувачі знають, що існують спеціальні функції, що дозволяють знайти на аркуші клітинку, яка містить певні дані, і повернути в якості результату вміст іншої комірки До функцій пошуку відносяться ВПР і ГПР

Функція ВПР має кілька аргументів:

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp шукане значення (адреса комірки)

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp таблиця (діапазон комірок, в яких слід проводити пошук і з яких

буде повернуто значення)

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp номер стовпця (номер стовпчика в зазначеній таблиці, в якому міститься

повертається значення)

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp інтервальний перегляд (логічне значення, що визначає, точно або при-

наближенні повинен проводитися пошук)

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

На рис 212 показаний приклад підготовки масиву даних, в яких виконуватиметься пошук Результат пошуку показаний на рис 213 Осередки A4, B4, C4 заповнюються результатами пошуку з використанням функції ВПР

Рис 212 Масив даних для пошуку

Рис 213 Використання функції ВПР

Функція ГПР діє аналогічно, але пошук відбувається не по вертикалі, а по горизонталі

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

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ПОИСКПОЗ (повертає відносне положення елемента, який відпо-

ствует вказаною значенням у зазначеному порядку)

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp СЧЕТЕСЛІ (підраховує всередині діапазону кількість осередків, задовольняю-

щих заданому критерію)

✓&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ІНДЕКС (повертає значення або посилання на нього з таблиці або діапазону

зона)

Функція ІНДЕКС має дві синтаксичні форми: посилання і масив

Всі ці функції корисні при автоматизації заповнення бухгалтерських бланків Рис 214 ілюструє застосування функції ІНДЕКС та інших функцій В осередку D1 знаходиться виклик функції = СЧЕТЕСЛІ (A1: A8 Грачов), а в комірці D2 – функції = СЧЕТЕСЛІ (B1: B8 <20 000"). У першому випадку так обчислюється кількість повторів в діапазоні клітинок A1: A8 значення Грачов. У цьому прикладі не перетворюються рядкові і прописні символи, тому відбувається точне порівняння рядка символів. Наприклад, якщо задати для пошуку рядок Грачов, то вона не буде знайдена. У другому випадку серед осередків B1: B8 обчислюється кількість значень, менших 20 000. У комірці F1 за формулою = ВПР (C1; A1: B7; 2; БРЕХНЯ) по вмісту комірки C1 в діапазоні A1: B7 шукається відповідний рядок, і функція повертає значення з другого шпальти зазначеного діапазону. Осередок H1 містить функцію = ПОИСКПОЗ (F1; B1: B7; 0). Так, в діапазоні клітинок B1: B7 шукається позиція комірки, що містить значення комірки F1. В осередку J1 відбувається обчислення за формулою

= ЕСЛИ (ІНДЕКС (A1: B7 H1 1) = C1 ІНДЕКС (A1: B7 H1 2) “) Перший виклик функції ІНДЕКС по вмісту комірки $ H $ 1 виконує пошук в діапазоні A1: B7 Якщо при цьому повертається значення, рівне вмісту осередку C1 (з нею порівнюється значення першого шпальти зазначеного діапазону), то в комірку J1 записується значення з другого шпальти знайденої рядка В іншому випадку в комірку J1 вводиться порожній рядок

Рис 214 Використання функції ІНДЕКС

Практичне використання функцій і формул при обчисленнях часто супроводжується додатковими перевірками вмісту осередків, результатів обчислень та ін У цих випадках необхідно використовувати логічні функції Найбільш вживані з них – функції І, АБО, ЯКЩО, НЕ Їх використання не представляє особливої ​​складності Всі функції мають приблизно однакові правила використання:

І (логіческое_значеніе1 логіческое_значеніе2 ..), АБО (логіческое_значеніе1 логіческое_значеніе2 ..), ЕСЛИ(лог_выражениезначение_если_истиназначение_если_ложь), НЕ (логічне_значення)

Джерело: Трусов А Ф Excel 2007 для менеджерів та економістів: логістичні, виробничі та оптимізаційні розрахунки (+ CD) – СПб: Питер, 2009 – 256 с: Ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*