Створення формул для обробки даних в електронній таблиці Excel

Створення формул для обробки даних в електронній таблиці Excel

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

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

Тип даних, що розміщуються в осередку, визначається автоматично при введенні. Якщо ці дані можна інтерпретувати як число, програма Excel так і робить. В іншому випадку дані розглядаються як текст. Введення формули завжди починається з символу «=» (знаку рівності).

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

Щоб завершити введення, зберігши введені дані, використовують кнопку Введення в рядку формул або клавішу ENTER. Щоб скасувати внесені зміни і відновити колишнє значення клітинки, використовують кнопку Скасування в рядку формул або клавішу ESC. Для очищення поточної клітинки або виділеного діапазону найпростіше використовувати клавішу DELETE.

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

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

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

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

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

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

Рис. 3. Діалогове вікно в розгорнутому і згорнутому вигляді

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

Абсолютні і відносні посилання. Типово, посилання на клітинки у формулах розглядаються як відносні. Це означає, що при копіюванні формули адреси в посиланнях автоматично змінюються відповідно до відносним розташуванням вихідної комірки і створюваної копії.

Нехай, наприклад, в комірці В2 є посилання на клітинку A3. У відносному поданні можна сказати, що посилання вказує на клітинку, яка розташовується на один стовпець ліворуч і на один рядок нижче запропонованої. Якщо формула буде скопійована в іншу клітинку, то таке відносне вказівку посилання збережеться. Наприклад, при копіюванні формули в комірку ЕА27 посилання буде продовжувати вказувати на клітинку, розташовану лівіше і нижче, в даному випадку на клітинку DZ28.

При абсолютній адресації адреси посилань при копіюванні не змінюються, так що осередок, на яку вказує посилання, розглядається як нетабличного. Для зміни способу адресації при редагуванні формули треба виділити посилання на клітинку та натиснути клавішу F4. Елементи номера осередку, що використовують абсолютну адресацію, предваряются символом $. Наприклад, при послідовних натисканнях клавіші F4 номер комірки А1 буде записуватися як А1, $ А $ 1, А $ 1 і $ А1. У двох останніх випадках один з компонентів номера осередку розглядається як абсолютний, а інший - як відносний.

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

Метод перетягування. Щоб методом перетягування скопіювати або перемістити поточну клітинку (виділений діапазон) разом із вмістом, слід навести покажчик миші на рамку поточної комірки (він набуде вигляду стрілки з додатковими стрілками). Тепер клітинку можна перетягнути в будь-яке місце робочого аркуша (точка вставки позначається спливаючій підказкою). Для вибору способу виконання цієї операції, а також для більш надійного контролю над нею рекомендується використовувати спеціальне перетягування за допомогою правої кнопки миші. У цьому випадку при відпусканні кнопки миші з'являється спеціальне меню, в якому можна вибрати конкретну виконувану операцію.

Застосування буфера обміну. Передача інформації через буфер обміну має програмі Excel певні особливості, пов'язані зі складністю контролю над цією операцією. Спочатку необхідно виділити копійований (вирізаний) діапазон і дати команду на його приміщення в буфер обміну: Правка> Копіювати або Правка> Вирізати. Вставка даних в робочий лист можлива лише негайно після їх приміщення в буфер обміну. Спроба виконати будь-яку іншу операцію призводить до скасування розпочатого процесу копіювання або переміщення. Однак втрати даних не відбувається, оскільки «вирізані» дані видаляються з місця їх початкового розміщення лише в момент виконання вставки.

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

Автоматизація вводу. Оскільки таблиці часто містять повторювані або однотипні дані, програма Excel містить засоби автоматизації введення. До числа наданих коштів відносяться: автозавершення, автозаповнення числами і автозаповнення формулами.

Автозавершення. Для автоматизації введення текстових даних використовується метод автозавершення. Його застосовують при введенні в комірки одного стовпця аркуша текстових рядків, серед яких є повторювані. У ході введення текстових даних у чергову клітинку програма Excel перевіряє відповідність введених символів рядках, наявному в цьому стовпці вище. Якщо виявлено однозначне збіг, введений текст автоматично доповнюється. Натиснення клавіші ENTER підтверджує операцію автозавершення, у противному випадку введення можна продовжувати, не звертаючи уваги на запропонований варіант.

Можна перервати роботу засобу автозавершення, залишивши в стовпці порожню комірку. І навпаки, щоб використовувати можливості засобу автозавершення, заповнені осередки повинні йти підряд, без проміжків між ними.

Автозаповнення числами. При роботі з числами використовується метод автозаповнення. У правому нижньому кутку рамки поточної комірки є чорний квадратик - маркер заповнення. При наведенні на нього покажчик миші (він зазвичай має вигляд товстого білого хреста) набуває форми тонкого чорного хрестика. Перетягування маркера заповнення розглядається як операція «розмноження» вмісту комірки в горизонтальному або вертикальному напрямку.

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

Нехай, наприклад, осередок А1 містить число 1. Наведіть вказівник миші на маркер заповнення, натисніть праву кнопку миші та перетягніть маркер заповнення так, щоб рамка охопила осередки А1, В1 і С1, і відпустіть кнопку миші. Якщо тепер вибрати в меню пункт Копіювати клітинки, всі осередки будуть містити число 1. Якщо ж вибрати пункт Заповнити, то осередках виявляться числа 1, 2 і 3.

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

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

Для прикладу припустимо, що значення в третьому стовпці робочого аркуша (стовпці С) обчислюються як суми значень у відповідних осередках стовпців А і В. Введемо в комірку С1 формулу = А1 + В1. Тепер скопіюємо цю формулу методом автозаповнення у всі комірки третього стовпця таблиці. Завдяки відносній адресації формула буде правильною для всіх осередків даного стовпця.

У таблиці 1 наведені правила оновлення посилань при автозаповнення уздовж рядка або уздовж стовпця.

Таблиця

Правила оновлення посилань при автозаповнення

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

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

Використання майстра функцій. При виборі пункту Інші функції запускається Майстер функцій, що полегшує вибір потрібної функції. У списку Категорія вибирається категорія, до якої належить функція (якщо визначити категорію важко, використовують пункт Повний алфавітний перелік), а в списку Виберіть функцію - конкретна функція даної категорії. Після клацання на кнопці ОК ім'я функції заноситься в рядок формул разом з дужками, що обмежують список параметрів. Текстовий курсор встановлюється між цими дужками. Викликати Майстер функцій можна й простіше, клацанням на кнопці Вставка функції в рядку формул.

Аргументи функції. Як тільки ім'я функції вибрано, на екрані з'являється діалогове вікно Аргументи функції (в попередніх версіях Excel це вікно розглядалося як палітра формул). Це вікно, зокрема, містить значення, яке вийде, якщо негайно закінчити введення формули (рис. 4).

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

Рис. 4. Рядок формул і діалогове вікно Аргументи функції