Роздрукувати сторінку

Главная \ Методичні вказівки \ Методичні вказівки \ 1493 Реалізація умовних конструкцій функції ЕСЛИ(), СЧЕТЕСЛИ(), СУММЕСЛИ() та умовне форматування

Реалізація умовних конструкцій функції ЕСЛИ(), СЧЕТЕСЛИ(), СУММЕСЛИ() та умовне форматування

« Назад

Реалізація умовних конструкцій

(функції ЕСЛИ(), СЧЕТЕСЛИ(), СУММЕСЛИ()
та умовне форматування)

Теоретичні відомості та рекомендації для студентів

Вступ

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

Підрахунок кількості комірок, які задовольняють заданому критерію

Для підрахунку кількості комірок у діапазоні, які задовольняють певному критерію, використовують функцію СЧЁТЕСЛИ(). Функція СЧЁТЕСЛИ() (також допускається правопис СЧЕТЕСЛИ) має 2 параметри з таким синтаксисом:

СЧЁТЕСЛИ(Діапазон; Критерій)

Тут Діапазон задає прямокутну множину комірок, які беруться функцією до розгляду. Розривні діапазони не підтримуються. Значеннями у комірках можуть бути числові дані у всіх різновидах, логічні значення, слова і фрази (комірки можуть бути також порожніми). Допустимі значення можна задавати як константою, так і формулою.
Критерій — це вираз з умовою для значень у комірках діапазону. Вираз (умова) представляється

- або точним значенням, якому і мають задовольняти комірки (наприклад, СЧЁТЕСЛИ(E16:E24; 200) підрахує кількість лише комірок із значенням 200),

- або виразом, найтиповіше відношенням рівності/нерівності, яке, будучи вирахуваним, давало б значення ІСТИНА чи НЕ_ІСТИНА (фактично, ИСТИНА та ЛОЖЬ) (наприклад, СЧЁТЕСЛИ(E16:E24;">=200") задає критерій у вигляді відношення нерівності). У разі визначення критерію у вигляді відношення, його форма має бути задана за правилами з’єднання тексту у формулі (див. приклади нижче).

Зверніть увагу:

- як у функції СЧЁТЕСЛИ(), так і у всіх наступних розглядуваних функціях (і взагалі у всіх функціях Excel) параметри відокремлюються один від одного символом «;» (крапка з комою);

- початкове значення функції СЧЁТЕСЛИ() за відсутності задовольняючих критерій комірок у діапазоні, природно, дорівнює нулю;

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

Нижче наведено варіанти завдання критеріїв (як для СЧЁТЕСЛИ(), так і для всіх подальших функцій):

- значення має дорівнювати числу, наприклад, 1207: СЧЁТЕСЛИ(Діапазон;"=1207") чи СЧЁТЕСЛИ(Діапазон;1207);

- значення має дорівнювати фразі, наприклад, «автомобіль»: СЧЁТЕСЛИ(Діапазон; "автомобіль");

- значення діапазону комірок C7:E7 має дорівнювати значенню у комірці, наприклад, Е8: СЧЁТЕСЛИ(C7:E7; Е8);

- значення має задовольняти нерівності (<, >, <=, >=, <>) із числом у вигляді константи: СЧЁТЕСЛИ(C7:E7; ">=1207");

- значення має задовольняти нерівності (<, >, <=, >=, <>) із значенням деякої комірки, наприклад, Е8: СЧЁТЕСЛИ(C7:E7;">=”&E8);

- для більш складних умовних виразів, коли задіються багато значень, потрібно використовувати функції И(Pr;Pr;…) та ИЛИ(Pr;Pr;…), де Pr — умовний вираз. Ці функції мають бути вкладені до функції типу СЧЁТЕСЛИ() та інші на місці критерію.

Підрахунок суми комірок, які задовольняють заданому критерію

Для підрахунку суми комірок діапазону, які задовольняють заданому критерію, використовують функцію СУММЕСЛИ(). Функція СУММЕСЛИ() має 3 параметри з наступним синтаксисом:

СУММЕСЛИ (Діапазон; Критерій; Діапазон_Сумування)

де Діапазон задає прямокутну множину комірок, які беруться функцією до розгляду. Розривні діапазони не підтримуються.
Критерій є вираз з умовою для значень у комірках діапазону. Задається аналогічно функції СЧЁТЕСЛИ().
Діапазон_Сумування визначає діапазон комірок, в якому, власне, виконується додавання значень комірок, для яких відповідні комірки у параметрі Діапазон задовольнили критерію. Параметр Діапазон_Сумування може не вказуватися, — тоді береться сума комірок у Діапазон (які, звичайно ж, задовольняють критерію).
Розглянемо приклади:

- у діапазоні C7:J7 знайти суму комірок, значення в яких має бути не менше за, наприклад, 1207: СУММЕСЛИ(C7:J7;">=1207");

- у діапазоні C12:J12 знайти суму лише тих комірок, для яких відповідні їм комірки у C7:J7 мають значення, наприклад, 1207:

СУММЕСЛИ(C7:J7; 1207; C12:J12);

- аналогічні діапазони комірок, як у прикладі вище, але у критерії будемо вимагати, щоб значення у C7:J7 не дорівнювали значенню у комірці F17:

СУММЕСЛИ(C7:J7;"<>"&F17;C12:J12).

Вибір одного значення із фіксованої множини значень

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

Функція ЕСЛИ() має 3 параметри з наступним синтаксисом:

ЕСЛИ(Умова; Значення_для_Істина; Значення_для_НЕ_Істина)

де Умова — будь-який вираз, значення якого буде або ИСТИНА, або ЛОЖЬ.
Значення_для_Істина є значення чи вираз, який поверне функція ЕСЛИ() за істинне значення параметра Умова. Якщо параметр не вказати, то як результат ЕСЛИ() буде повертатися ИСТИНА. Виразом у параметрі, зокрема, може бути будь-яка інша функція, як і ЕСЛИ() так само.
Значення_для_НЕ_Істина представляє значення чи вираз, який поверне функція ЕСЛИ() за не істинне значення параметра Умова. Якщо параметр не вказати, то як результат ЕСЛИ() буде повертатися ЛОЖЬ. Виразом у параметрі, зокрема, може бути будь-яка інша функція, так само і ЕСЛИ().
Зверніть увагу:

- якщо у функції ЕСЛИ() пропускається другий параметр, то символ «;» перед третім параметром має бути обов’язково:

ЕСЛИ(Умова; ;Значення_для_НЕ_Істина);

- функція ЕСЛИ() допускає вкладення функцій ЕСЛИ() одна в одну до семи разів.

Розглянемо приклади:

- ЕСЛИ(C7>J7–15,39;"У балансі є помилка!";"") потрібно розуміти так: при умові, що значення комірки C7 більше від комірки J7, зменшеної на величину 15,39, то значенням комірки буде текст "У балансі є помилка!". Якщо ж ця умова не виконується — значенням комірки буде порожній рядок "";

- ЕСЛИ(C7>J7–15,39;"У балансі є помилка!"; ЕСЛИ(C7=2002;"У балансі можлива помилка!"; "Баланс нормальний!")) використовує вкладення функцій ЕСЛИ(). У самій першій ЕСЛИ() для випадку підстановки, як результату, третього параметру буде розраховуватися ще одна ЕСЛИ().

Умовне форматування

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

Для застосування до комірки/діапазону умовного форматування:

- виділіть комірку/діапазон;

- в меню Формат виберіть пункт Формат/Условное форматирование. Відкривається форма діалогу Условное форматирование;

- у формі групою управляючих елементів пропонується задати першу умову застосування форматування. Для кожної умови (від 1 до 3-х) може бути свій варіант форматування;

- для умови можна вибрати або (1) умову для значення комірки, або (2) задати формулу (комірку з формулою). Для значення пропонуються всі основні варіанти відношення (=, <, >, <>, <=, >=, поза відрізком значень) у списку вибору відношення, що випадає. В залежності від вибраного відношення, з’являється правіше списку відношень необхідна кількість полів для значень чи посилань на комірки із значеннями;

- формула, як критерій застосування умовного форматування, використовується для звернення до будь-яких функцій та виразів, які повертають значення ИСТИНА або ЛОЖЬ;

- задавши умову форматування, натискаємо кнопку Формат. Відкривається форма діалогу Формат ячеек;

- ця форма аналогічна відомій вам формі, яка викликається через меню Формат/Ячейки;

- задаємо формат: 1) шрифту, 2) границь та 3) тла комірки;

- якщо потрібна ще одна умова застосування для комірки/діапазону умовного форматування (тепер вже 2-га чи 3-я), натискаємо кнопку А также;

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

Варіанти завдань

Варіант 1

Скопіюйте з сервера у власний каталог файл Лаб_роб_8_1.xls та відкрийте його. У файлі знаходиться таблиця «Штат працівників фірми» із такими даними (стовпчиками): Прізвище, Ім’я, По-батькові, Стать («ч» для чоловічої та «ж» для жіночої), Рік народження (4 цифри), Оклад (грн.).
Окремими рядками нижче таблиці вивести такі рядки: Чоловіків – число та Жінок – число.
Окремим рядком нижче таблиці вивести кількість працівників із роком народженням, як у працівника за № 1.
Окремим рядком нижче таблиці вивести кількість працівників із віком від 30 років.
Підрахувати кількість Андріїв та Олен серед працівників.
Підрахувати середній вік працівників серед чоловіків та жінок.
Підрахувати середній оклад працівників серед чоловіків та жінок.
Підрахувати середній оклад працівників до 40 років та від 40 років.
Створити новий стовпчик у таблиці, в якому для кожного працівника вказується відхилення його окладу від середнього по фірмі (це має бути число зі знаком).
Створити новий стовпчик Преміювання у таблиці, в якому нарахувати премію у %% від їх окладу для ювілярів: 1) 20 років — 120%; 2) 30 років — 150%; 3) 40 років — 200%; 4) 50 років — 250%; 5) 60 років — 300%.
Виконати умовне форматування у колонці Преміювання таблиці так, щоб значення премій від 1000 грн. були виділені червоним кольором.

Варіант 2

Скопіюйте з сервера у власний каталог файл Лаб_роб_8_2.xls та відкрийте його. У файлі знаходиться таблиця "Обсяги виробництва компанії" із такими даними (стовпчиками): Назва товару, Об’єм реалізації 3 роки назад (штук та грн.), Об’єм реалізації 2 роки назад (штук та грн.), Об’єм реалізації 1 рік назад (штук та грн.), Об’єм поточного року (штук та грн.).
Побудувати в рядку нижче таблиці значення числа років, коли продаж продукції Моторола переважала продаж Еріксон у поточному році.
Знайти кількість років, коли апарати Сіменс продавалися не менше 1000 шт. на рік.
У компанії вживають термін "місцевий бренд" для апаратів з продажем не менше 1000 шт. на рік. Знайти значення кількості місцевих брендів для кожного року та відобразити їх окремим рядком у нижній частині таблиці.
У рядку із кількістю "місцевих брендів" для кожного року (попереднє завдання) внести такі зміни, щоб у комірках із нулями (немає "брендів") були порожні комірки (що покращує читабельність таблиць).
Побудувати в рядку нижче таблиці рядок "Продано апаратів назва: nnnn (шт.)", в якому задавши назву виробника телефонів отримаємо кількість nnnn продаж за всі роки.
Знайти %% виручених коштів за продаж всіх апаратів понад 1000 шт./рік від всіх коштів.
Додати до таблиці новий стовпчик, в якому лише для "місцевих брендів" вказується приріст (число зі знаком) продажу (грн.) у поточному році до попереднього.
Додати до таблиці новий стовпчик для даних за поточний рік, в якому знак '+' стоїть лише для тих реалізацій (штук), що перевищують попередній рік.
Побудувати в рядку нижче таблиці рядок, в якому під коміркою об’ємів реалізації (грн.) за відповідний рік, пишеться фраза 'Успішний', якщо реалізації перевищили попередній рік та 'Не успішний' в разі зменшення реалізацій.
Задайте умовне форматування для всіх комірок реалізацій (шт.), щоб всі значення від 2000 шт. виділялися червоним кольором.

Варіант 3 (ускладнений)

Скопіюйте з сервера у власний каталог файл Лаб_роб_8_3.xls та відкрийте його. У файлі знаходиться таблиця "Характеристика регіонів України" із такими даними (стовпчиками): Назва регіону, Площа регіону, Дані про населення для 1980 р., 1990 р. та 2000 р. у такій деталізації: а) всього, б) % міського населення, в) % жіночого населення; г) народжуваність дітей/1000 сімей, Обсяги виробництва (вал, у млн. грн.) для 1980 р., 1990 р. та 2000 р. у такій деталізації: а) всього, б) % сільського господарства, в) % державного сектору; г) % приватного сектору.
Підрахуйте кількість регіонів із площею, яка: а) перевищує площу 1-го регіону; б) вдвічі перевищує площу 1-го регіону.
Знайти кількість регіонів, для яких у поточному році %% міського населення не перевищував 55%.
Підрахуйте кількість регіонів, в яких %% чоловічого населення складає до 45%.
Знайти валові обсяги виробництва у 1990 р. лише для тих регіонів, де %% жіночого населення складає до 55%. Зробіть зміни даних з %% жіночого населення. Чи змінюється розрахований показник?
Знайти кількість населення у 1990 р. для тих регіонів, які у 2000 р. мали частку приватного сектору від 25%.
Знайти обсяги сільгоспвиробництва для кожного року за тими регіонами, в яких %% міського населення на той період перевищував % самого 1-го регіону. Дані представити окремим рядком знизу таблиці.
Знайти середню чисельність населення для 2000 р. в тих регіонах, в яких %% міського населення від 60%.
Створити стовпчик у таблиці, в комірках якого має бути фраза "депопуляція" для регіонів з народжуваністю на 2000 р. до 150 дітей на 1000 сімей.
Виділити синім кольором значення народжуваності для 1990 р. для регіонів, де цей показник менше 200. Цей же показник із значеннями до 200 для 2000 р. виділити червоним кольором.

Варіант 4

Скопіюйте з сервера у власний каталог файл Лаб_роб_8_4.xls та відкрийте його. У файлі знаходиться таблиця "База клієнтів" із такими даними (стовпчиками): Ім’я/найменування клієнта, Ознака особи: 'ю' –для юридичних осіб та 'ф' –для фізичних осіб, Дата 1-го замовлення, Дата останнього замовлення, Кількість оплачених замовлень, Кількість неоплачених замовлень, Об’єм оплачених замовлень (грн.).
Знайти кількість клієнтів серед юридичних осіб.
Знайти кількість клієнтів серед фізичних осіб, у яких останнє замовлення припадає на поточний рік.
Знайти кількість клієнтів, які мають більше ніж 10 несплачених замовлень.
Знайти кількість клієнтів, які стали клієнтами у заданому деякою коміркою (поза таблицею) роком.
Знайти кількість клієнтів, у яких середня вартість замовлення не менша від 10 тис грн.
Знайти середню вартість замовлення від юридичних осіб.
Знайти кількість несплачених замовлень всіма фізичними особами.
Знайти середню вартість замовлення від фізичних осіб.
Створити стовпчик "Цінний клієнт" у таблиці, в комірках якого має бути знак '+' для клієнтів із замовленнями від 100 тис. грн.
Виділити червоним кольором ім’я/назви клієнтів у яких кількість несплачених замовлень не менша за сплачені.

Варіант 5

Скопіюйте з сервера у власний каталог файл Лаб_роб_8_5.xls та відкрийте його. У файлі знаходиться таблиця "Товарна номенклатура" із такими даними (стовпчиками): Найменування товару, Шифр товару (4 цифри), Об’єм нереалізованих замовлень (шт.), Реалізовано у поточному році (шт.), Вартість реалізованого товару (грн.).
Знайти кількість товарних позицій, для яких більше ніж 100 нереалізованих замовлень.
Знайти кількість товарних позицій із шифрами, старшими за 2107.
Знайти %% товарних позицій, у яких є нереалізовані замовлення до 50 шт.
Знайти вартість реалізованого товару, шифр якого не перевищує 2107.
Знайти об’єм нереалізованих замовлень для товару із реалізацією понад 1000 шт.
Знайти %% нереалізованих замовлень від реалізованих для товарної групи із шифрами >2107.
Створити стовпчик у таблиці, в комірках якого має бути знак оклику '!', якщо для відповідного товару є від 100 нереалізованих замовлень.
Створити у таблиці стовпчик "Середня вартість товару", в якому є відповідні значення лише для товару із шифром >2107.
Виділити синім кольором шрифту у колонці "Вартість реалізованого товару" ті комірки, значення яких перевищує 50 тис. грн.
Виділити зеленим кольором шрифту назви товару із шифром до 2107.

Варіант 6

Скопіюйте з сервера у власний каталог файл Лаб_роб_8_6.xls та відкрийте його. У файлі знаходиться таблиця "Автотранспортне господарство" із такими даними (стовпчиками): Бортовий № машини, Шифр транспортного засобу (4 цифри), Код виду транспорту: 'в' –для вантажівок, 'м' –для мікроавтобусів, 'а' –для автомобілів, Пробіг (км), Рік останнього капремонту, Середні витрати пального (літрів/на 100 км).
Знайти кількість транспортних одиниць, пробіг яких перевищує 100 тис. км.
Знайти кількість мікроавтобусів, рік капремонту яких є після 2000 р.
Знайти кількість транспортних одиниць із середніми витратами пального від 10 літрів.
Знайти кількість транспортних одиниць, шифр яких більший за значення, записане в деякій комірці поза таблицею.
Знайти середній пробіг вантажівок
Знайти сумарний пробіг для мікроавтобусів.
Знайти %% середніх витрат пального всіх автомобілів, всіх мікроавтобусів від всіх вантажівок (останні беруться за 100%).
Створити стовпчик у таблиці, в комірках якого для відповідного транспортного засобу з’являється фраза "Капремонт", якщо останній капремонт не був у поточному році та пробіг від 500 тис. км.
У новому стовпчику із попереднього завдання фраза "Капремонт" має бути під кутом у 45⁰, якщо пробіг понад 700 тис. км.
У стовпчику "Пробіг" виділити значення червоним кольором для тих транспортних засобів, пробіг яких вже понад 500 тис. км.

Контрольні запитання

Яким чином задається вибір комірок із множини останніх у вигляді прямокутного діапазону комірок?
Чи допускає функція СЧЁТЕСЛИ() роботу із розривними діапазонами?
Яка кількість вкладених конструкцій ЕСЛИ може бути у функції ЕСЛИ()?
Які існують варіанти завдання критеріїв для розглянутих вище функцій?
Чи є помилка у запису СЧЁТЕСЛИ(E16:E24; >=200) ?
Яка кількість параметрів у функції СУММЕСЛИ()?
Яку кількість варіантів форматування можна задати в умовному форматуванні?

З повагою ІЦ "KURSOVIKS"!