Тема: Використання формул при обчисленнях в таблицях. Створення формул
Мета: навчити учнів створювати та застосовувати формули при обчисленнях в таблицях.
Методичне забезпечення: ПК із програмою.
Тип уроку: комбінований.
СТРУКТУРА УРОКУ
1. Організаційний момент
2. Актуалізація опорних знань
3. Пояснення нового матеріалу
План
Поняття формули. Створення формули.
Переміщення та копіювання формул.
Посилання на комірки.
Використання авто обчислень.
Введення в формули дат і часу.
-
Поняття формули. Обчислення формул.
Формула - це сукупність математичних операцій, що виконуються над даними в робочому листі: додавання, віднімання, множення, ділення.
Формули обов'язково починаються знаком = і крім адресів комірок містять знаки математичних операцій: , , , , , ..
Коли встановлено автоматичний режим обчислень (цей режим встановлюється за замовчуванням), зміна вмісту комірок веде до перерахунку формул, які використовують ці комірки. Для встановлення ручного режиму обчислень слід у вкладці Вычисления діалогового вікна Параметры у блоці Производить перерасчет встановити режим Вручную. У цьому режимі Excel виводить слово Вычислить у рядок стану всякий раз, коли в листі з’являється необчислена формула. Для переобчислення формул слід натиснути клавішу F9 (формули переобчислення в усіх відкритих книгах) або Shift+F9 (формули переобчисляться тільки в активному листі). У полі комірки може відображатись або формула, або значення. Вид відображення залежить від значення опції Формула вкладки Вид вікна діалогу Параметры. При встановленій опції в комірці буде відображуватись формула.
Якщо при обчисленні формули сталася помилка, то в комірку виводиться повідомлення про помилку, яке починається з символу "#". Excel виводить такі повідомлення про помилки: # дел 0 - спроба поділити на нуль або на порожню комірку; # имя ? - формула використовує неіснуюче ім’я; # н/д - формула посилається на комірку з невизначеними даними; # число ! - помилка у числі, число не можливо подати в Excel ; # ссыл ! - формула посилається на неіснуючу комірку; #знач ! - помилка при обчисленні функції.
Створення формули, введеної в клітинку за допомогою клавіатури. Для створення формули, введеної в клітинку, яка складається із координат клітинок і числових операторів, виконайте такі кроки:
1. Виділіть клітинку, в яку потрібно ввести формулу.
2. На початку формули наберіть знак рівності “=”.
3. Наберіть адреси клітинок, що містять значення, над якими проводяться обчислення, і введіть потрібний оператор.
4. Натисніть клавішу Enter. Excel покаже результат обчислення в активній клітинці і формула з’явиться в рядку формул.
Створення формул із заданням клітинок: задання клітинок за допомогою миші.
Для задання клітинок формули за допомогою миші виконайте такі дії:
1. Виділіть клітинку, в яку потрібно ввести формулу.
2. На початку формули наберіть знак рівності”=”.
3. Клацніть лівою кнопкою миші на клітинці, адресу якої потрібно ввести у формулу.
4. Наберіть потрібний знак математичної операції.
5. Клацніть на наступній клітинці, адресу якої потрібно ввести в формулу.
6. Для виконання формули клацніть на кнопці вводу (галочка) в рядку формули або натисніть клавішу Enter.
-
Переміщення та копіювання формул.
Програма Microsoft Excel призначена для роботи з таблицями даних, переважно числових. При формуванні таблиці виконують введення, редагування та форматування текстових і числових даних, а також формул. Наявність засобів автоматизації полегшує ці операції. Створена таблиця може бути виведена на друк.
Введення, редагування та форматування даних. Окрема осередок може містити дані, що відносяться до одного з трьох типів: текст, число або формула, - а також залишатися порожньою. Програма Excel при збереженні робочої книги записує у файл тільки прямокутну область робочих листів, що примикає до лівого верхнього кута (комірка А1) і містить всі заповнені комірки.
Тип даних, що розміщуються в осередку, визначається автоматично при введенні. Якщо ці дані можна інтерпретувати як число, програма Excel так і робить. В іншому випадку дані розглядаються як текст. Введення формули завжди починається з символу «=» (знаку рівності).
Введення тексту та чисел. Введення даних здійснюють безпосередньо в поточну клітинку або в рядок формул, розташовану у верхній частині вікна програми під панелями інструментів (див. рис. 2). Місце введення відзначається текстовим курсором. Якщо почати введення натисненням алфавітно-цифрових клавіш, дані з поточної комірки замінюються вводиться текстом. Якщо клацнути на рядку формул або двічі на поточній комірці, старе вміст вічка не видаляється і з'являється можливість його редагування. Введені дані в будь-якому випадку відображаються як у клітинці, так і в рядку формул.
Щоб завершити введення, зберігши введені дані, використовують кнопку Введення в рядку формул або клавішу ENTER. Щоб скасувати внесені зміни і відновити колишнє значення клітинки, використовують кнопку Скасування в рядку формул або клавішу ESC. Для очищення поточної клітинки або виділеного діапазону найпростіше використовувати клавішу DELETE.
Форматування вмісту комірок. Текстові дані за замовчуванням вирівнюються по лівому краю комірки, а числа - по правому. Щоб змінити формат відображення даних у поточній клітинці або вибраному діапазоні, використовують команду Формат> Ячейки. Вкладки цього діалогового вікна дозволяють вибирати формат запису даних (кількість знаків після коми, вказівку грошової одиниці, спосіб запису дати та інше), задавати напрямок тексту та метод його вирівнювання, визначати шрифт і накреслення символів, керувати відображенням і видом рамок, задавати фоновий колір.
Формули. Обчислення в таблицях програми Excel здійснюються за допомогою формул. Формула може містити числові константи, посилання на клітинки і функції Excel, з'єднані знаками математичних операцій. Дужки дозволяють змінювати стандартний порядок виконання дій. Якщо комірка містить формулу, то в робочому аркуші відображається поточний результат обчислення цієї формули. Якщо зробити комірку поточної, то сама формула відображається в рядку формул.
Правило використання формул у програмі Excel полягає в тому, що, якщо значення клітинки дійсно залежить від інших елементів таблиці, завжди слід використовувати формулу, навіть якщо операцію легко можна виконати в «умі». Це гарантує, що наступне редагування таблиці не порушить її цілісності і правильності вироблених в ній обчислень.
Посилання на клітинки. Формула може містити посилання, тобто адреси осередків, вміст яких використовується в обчисленнях. Це означає, що результат обчислення формули залежить від числа, що знаходиться в іншій клітинці. Осередок, що містить формулу, таким чином, є залежною. Значення, відображене у клітинці з формулою, перераховується при зміні значення клітинки, на яку вказує посилання.
Посилання на клітинку можна задати різними способами. По-перше, адреса комірки можна ввести вручну. Інший спосіб полягає в клацанні на потрібній комірці чи виборі діапазону, адреса якого потрібно ввести. Осередок або діапазон при цьому виділяються пунктирною рамкою.
Всі діалогові вікна програми Excel, які вимагають вказівки номерів або діапазонів клітинок, містять кнопки, приєднані до відповідних полях. При натисканні на такій кнопці діалогове вікно згортається до мінімально можливого розміру, що полегшує вибір потрібної комірки (діапазону) за допомогою клацання або протягування
Рис. 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. Тепер скопіюємо цю формулу методом автозаповнення у всі комірки третього стовпця таблиці. Завдяки відносній адресації формула буде правильною для всіх осередків даного стовпця.
3. Посилання на комірки.
Як відомо, адреса комірки включає назву колонки і номер рядка. Адреси комірок (посилання на комірки) можна використовувати у формулах. Можливі відносні, абсолютні і змішані посилання.
Посилання, яке включає назву колонки і номер рядка, є відносним. При копіюванні формули, а також редагуванні листа таке посилання буде модифікуватись. Наприклад, якщо в якійсь комірці є формула, яка посилається на комірку B4, то при вилученні, припустимо, рядка 2 і зсуву по таблиці вгору формула буде модифікуватись таким чином, щоб посилатись на комірку В3. В абсолютних посиланнях перед назвою колонки і номером рядка стоїть символ $. Такі посилання не модифікуються. У змішаних посиланнях абсолютною є назва колонки і відносно - номер рядка або навпаки (наприклад, $А1, А$1). У них модифікується тільки відносна частина посилання. У формулі можуть бути посилання на діапазон комірок. Діапазон може бути тільки прямокутним. Вказуючи діапазон комірок, задається адреса верхньої лівої клітки і через двокрапку - адреса нижньої правої клітки. Якщо у формулі є посилання на комірки, які знаходяться на іншому листі, то посилання повинно містити ім’я листа, знак оклику та адресу комірки, наприклад, лист1!А1.
4. Використання автообчислень.
У деяких випадках потрібно, наприклад, підсумувати значення певного виділеного діапазону клітинок, але немає потреби цього підсумовуючого значення відображати у робочому листі. Для цього використовують механізм автообчислення.
Виділіть діапазон клітинок у робочому листі, тоді у рядку стану даного вікна побачите значення суми цих чисел.
Якщо клацнути правою кнопкою миші на рядку стану, то відобразиться контекстне меню, із якого можна обрати будь-яку з функцій для автообчислень. Результат обчислень так само відображається у рядку стану вікна Excel.
5. Введення в формули дат і часу
Можна також створювати формули, за допомогою яких проводяться розрахунки з використанням дат і часу. Коли ви використовуєте дату і час у формулі, то необхідно ввести їх у форматі, прийнятому в Excel, і взяти їх у лапки. Потім програма виконає необхідні перетворення. Наприклад, щоб знайти кількість днів між двома числами, можна ввести таку формулу: = “25/3/05” - ”1/2/05”. В цьому прикладі результатом буде 52. Якщо Excel не розпізнає дату і час, це означає, що вони введені як текст. Це відображається у повідомленні про помилкове значення.
4. Закріплення вивченого матеріалу
1. Що таке формула?
2. Яким чином можна скопіювати формулу?
3. Як ввести в формулу дату й час?
4. Що таке автообчислення?
5. Підсумок уроку
6. Домашнє завдання
|