Використання підсумкових функцій

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

СУММ( діапазон1; діапазон2; ...) – функція обчислює суму чисел, що входять до списку діапазонів. Список може містити до 30 елементів. Якщо у вибраному діапазоні є нечислові дані, при розрахунках вони вважаються як нулі.

СРЗНАЧ( діапазон1; діапазон2; ...) – функція розраховує середнє арифметичне серед чисел вказаних діапазонів.

МИН( діапазон1; діапазон2; ...) – функція знаходить мінімальне значення серед чисел вказаних діапазонів.

МАКС( діапазон1; діапазон2; ...) – функція знаходить максимальне значення серед чисел вказаних діапазонів.

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

СЧЁТ( діапазон перегляду) – функціяпідраховує у діапазоні перегляду кількість комірок, що містять числові значення.

СЧЁТЕСЛИ( діапазон перегляду; умова пошуку) – функція виконує підрахунок у діапазоні перегляду кількості комірок, що задовольняють умові пошуку.

НАИБОЛЬШИЙ ( діапазон перегляду; номер) – функція виконує пошук найбільшого по рахунку елементу з вказаним номером від максимального значення у заданому діапазоні перегляду.

НАИМЕНЬШИЙ ( діапазон перегляду; номер) – функція виконує пошук найменшого по рахунку елементу з вказаним номером від мінімального значення у заданому діапазоні перегляду.

Використання функцій СУММЕСЛИ() та СЧЁТЕСЛИ() обмежено можливістю використання у них тільки однієї умови, яку можна внести у якості умови пошуку. Тому, якщо необхідно зробити вибірку за складною (з декількома умовами) умовою, ці функції не допоможуть. У цьому випадку використовують функції роботи з масивами.

Масив – це організована множина комірок або констант будь-якого типу. Масив може містити не тільки множину комірок, але й множину констант. Наприклад, вираз {3; -4; 12} описує масив констант з трьох числових елементів.

При роботі з масивами можна використовувати функції СЧЁТ(), МАКС(), СУММ() та інші підсумкові функції, включивши до них функцію ЕСЛИ() з необхідними умовами відбору. Умови можуть складатися не тільки з констант, але й з посилань на комірки. В умовах не можна використовувати логічні функції И(), ИЛИ(), НЕ(), Але можна використовувати вкладені функції. Якщо необхідно обчислити кількість деяких елементів, у якості аргументу функції ЕСЛИ() використовують 1, для обчислення суми – самі ті елементи, що містять необхідні дані.

Для завершення введення формули, що обробляє масив, необхідно натиснути клавіші CTRL+SHIFT+ENTER. При такому введенні формула автоматично обрамляється фігурними дужками.

Автоматична перевірка правильності введених даних

У програмі Excel існують засоби автоматичної перевірки правильності введення даних. Правила, що будуть використовуватися для підтвердження правильності введення, повинні бути розроблені заздалегідь. Виконати додаткову операцію по налагодженню умов правильності введення необхідно при підготовці робочої книги до введення даних. Для цього слід виділити область робочого листа, призначену для введення даних визначеного типу, та виконати команду меню Данные/Проверка. Умови, які накладаються на значення, що вводяться, задаються на вкладниці Параметри діалогового вікна Проверка вводимых значений (рис. 2)

Якщо у обраній комірці дозволено введення тільки значень із визначеного переліку, необхідно створити де-небудь на робочому листі список припустимих варіантів, а потім обрати у списку Тип данных пункт Список та вказати діапазон комірок, які містять список, у полі Источник. Тоді при введенні значення в комірку, поряд з нею з’явиться кнопка, що дозволяє розгорнути список припустимих значень. Можливість некоректного введення даних у цьому випадку повністю відсутня.

Інші пункти списку Тип данных дозволяють обрати інші типи даних та задати для них відповідні обмеження. Способи обрання обмежень залежать від того, які саме дані повинні бути занесені у комірку.

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

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

· Останов – сама жорстка реакція: тут можливі тільки дві дії – повтор введення (Повторить) або повне відмовлення від введення (Отмена)

· Предупреждение – користувач може ігнорувати повідомлення (Да) та залишити введене значення; повернутися для виправлення введення (Нет); зовсім відмовитися від нього (Отмена)

· Сообщение – користувач може залишити введені значення (ОК) або відмовитися від них (Отмена)

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

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

Для встановлення умов форматування необхідно виділити область робочого листа, призначену для форматування, та виконати команду меню Формат/Условное форматирование (рис. 3).

Умов може бути до трьох. Вікно кожної наступної умови відкривається за допомогою кнопки А также. Умови можуть бути двох типів:

- значение – дозволяє задати значення, відносно якого перевіряють комірки, що форматуються;

- формула – дозволяє вказати логічну формулу будь-якої складності.

Умови можуть містити не тільки константи, але й посилання як на окремі комірки так й на діапазони комірок.