17137

Формули. Арифметичні операції. Операторі відношення. Зведені таблиці. Список. Сортування, фільтр, форма

Лекция

Информатика, кибернетика и программирование

Лекція №13 Тема: Формули. Арифметичні операції. Операторі відношення. Зведені таблиці. Список. Сортування фільтр форма. План Формули. Арифметичні операції. Операторі відношення. Зведені таблиці. Сортування фільтр форма. Формули. Обчисленн

Украинкский

2013-06-29

181.5 KB

1 чел.

Лекція №13

Тема: Формули. Арифметичні операції. Операторі відношення. Зведені таблиці. Список. Сортування, фільтр, форма.

План

  1.  Формули.
  2.  Арифметичні операції.
  3.  Операторі відношення.
  4.  Зведені таблиці.
  5.  Сортування, фільтр, форма.

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

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

Формула починається із знаку рівності (=). Приведемо приклад формули, що умножає 2 на 3 і додаючої до результату 5.

=5+2*3

Приведені нижче формули містять відносні посилання на інші осередки і їх імена. Осередок, що містить формулу, називається залежним осередком, якщо її значення залежить від значень в інших осередках. Наприклад, осередок B2 є залежним, якщо вона містить формулу =C2.

Приклад формули

Опис

=C2

Використовує значення в осередку C2

=Лист2!B2

Використовує значення в осередку B2 на Лист2

=Ответственность-Актив

Віднімає осередок Відповідальність з осередку Актив

  1.  Клацніть осередок, в яку вимагається ввести формулу.
  2.  введіть = (знак рівності).
  3.  Виконайте одну з наступних дій.
    •  Щоб створити посилання, виділіть осередок, діапазон осередків, місце в іншому листі або місце в іншій книзі. Можна перемістити межу виділення, перетягнувши межу осередку, або перетягнути кут межі, щоб розширити виділення.

  •  Щоб створити посилання на іменований діапазон, натисніть клавішу F3, виберіть ім'я в полі Вставка імені і натисніть кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Зведені таблиці.

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

Зведені таблиці створюються спеціальним майстром, що активізується за командою Данные - Сводная таблица.... При цьому відкривається перше діалогове вікно, де призначається джерело даних і вид зведеного об'єкта.

У другому діалоговому вікні Майстра задається абсолютна адреса діапазону даних вихідної таблиці, що передбачається включити в зведену таблицю.

В останньому вікні Майстра визначається місце розташування зведеної таблиці. Натисненням кнопки «Макет...» цього вікна на екран викликається вікно Майстра зведених таблиць, в якому задається структура проектованої зведеної таблиці (рис. 1).

Рис. 1. Вікно діалогу «...макет»

У даному вікні містяться області даних, сторінок, стовпців і рядків, а також імена всіх полів вихідної таблиці. Обрані імена полів розміщуються в областях макета методом Drag and Drop. Порядок їхнього розміщення залежить від характеру задач, що покладаються на зведені таблиці і структури останніх.

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

Рис.2. Панель інструментів «Сводные таблицы»

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

Вибір типу операції здійснюється як при роботі зі зведеною таблицею, так і при формуванні її макета,

Створення зведених діаграм

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

Для цього досить виконати команду Сводная диаграмма меню Данные або клацнути лівою клавішею миші на однойменній кнопці панелі інструментів — .

При виборі опції «Все» у зведеній таблиці відображаються підсумки по датах усіх замовлень і загальний підсумок.

Натисненням кнопки «Отобразить детали» -  панелі інструментів «Сводные таблицы» або за командою Группа и структура - Отобразить детали контекстного меню в таблицю викликається додаткова інформація про приналежність її даних до окремих замовників і товарів.

Вибір поля, що містить елементи деталізації, здійснюється в діалоговому вікні «Показать детали», яке відкривається, якщо була активізована опція «Развертывание разрешено» вікна «Параметры сводной таблицы» третього кроку Майстра зведених діаграм.

Фільтрація

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

У Microsoft Excel доступні дві команди для фільтрації списків:

  •  Автофільтр, включаючи фільтр по виділеному, для простих умов відбору;
  •  Розширений фільтр для складніших умов відбору.

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

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

Microsoft Excel позначає відфільтровані елементи блакитним кольором

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

За допомогою команди Розширений фільтр можна фільтрувати список так само, як і за допомогою команди автофільтр, але при цьому не відображаються списки для стовпців, що розкриваються. Натомість в окремому діапазоні умов над списком вводяться умови, відповідно до яких вимагається провести фільтрацію. Діапазон умов дозволяє провести фільтрацію за складніших умов відбору.

Форма.

Команда Форма надає користувачу ще один спосіб проглядання даних, дозволяючи йому редагувати, видаляти і вводити нові записи.

  1.  Активізуйте будь-який осередок в списку і виберіть з меню Дані команду Форма.
  2.  З'явиться діалогове вікно форми. Ім'я в рядку заголовка відповідає назві листу робочої книги Excel. Зверніть увагу на те, що в лівій частині діалогового вікна перераховані імена всіх полів списку, а проти них стоять дані першого запису списку.

  1.  Для пошуку записів, що задовольняють певному критерію, клацніть на кнопці Критерії.
  2.  Далі клацніть на рядку, дані в якій будуть критерієм. Після цього введіть значення, по яких хочете провести відбір. Наприклад, щоб знайти запис про заробітну платню Ірини Сахно введіть в полі Прізвище слово Сахно. Можна також знайти всі записи, в яких прізвище починається з букви З, ввівши в цьому рядку комбінацію С*).

У діалоговому вікні Форма, крім проглядання вибраних записів, можна також додавати нові записи. Для цього потрібно клацнути на кнопці Додати і ввести дані в порожні поля, що з'явилися, після чого натиснути клавішу Enter. У цьому діалоговому вікні можна також видаляти дані. Для цього знайдіть запис, що видаляється, і клацніть на кнопці Видалити. Нарешті, це вікно дозволяє також редагувати записи. Для цього виберіть в ньому потрібний запис, внесіть необхідні зміни і натисніть Enter.

  1.  Клацніть на кнопці Далі, якщо хочете, щоб пошук проводився нижче поточному запису, і на кнопці Назад, якщо пошук повинен проводитися перед поточним записом. Щоб повторити пошук з тими ж критеріями, необхідно повторно клацнути на одній з цих кнопок.
  2.  Щоб знову дістати можливість проглядання всіх записів, клацніть на кнопці Критерії і потім — на кнопці Очистити, внаслідок чого всі критерії будуть відмінені. Після цього клацніть на кнопці Повернути. Тепер можна, використовуючи кнопки Далі і Назад, проглядати всі записи в списку.
  3.  Можна також знайти набір записів за допомогою операторів порівняння (=, <, >, <=, >= і про). Наприклад, можна, клацнувши на кнопці Критерії, ввести в полі Зарплата 2001 р. критерій >=5000, після чого за допомогою кнопок Далі і Назад проглянути записи всіх співробітників, чия заробітна платня перевищує 5000 р.

  1.  Після того, як проглядання записів закінчене, можна закрити діалогове вікно Форма, клацнувши на кнопці Закрити або на стандартній кнопці закриття вікна, розташованої в його верхньому правому кутку.

На зміни, які можна внести в запис в діалоговому вікні Форма, накладається ряд обмежень. Нові записи можна додавати тільки в кінець списку. Крім того, можна змінювати значення тільки тих осередків, які містять дані. Значення осередків, що містять формули з назви полів виводяться як текст на сірому форі діалогового вікна. Їх змінити не можна.

Література:

 Уокенбах Д. Excel 2002. Библия пользователя. М.: Издательский дом «Вильямс», 2004. – 832с. [7], 172-193

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

  1.  Як побудувати зведену таблицю за допомогою програми Excel.
  2.  Яки існують відмінності сортування від фільтрації?


 

А также другие работы, которые могут Вас заинтересовать

42263. Экспертные системы. Продукционные экспертные системы 67 KB
  Экспертные системы интеллектуальная программа способная делать логические выводы на основании знаний в конкретной предметной области и обеспечивающая решение специфических задач.
42264. ИЗУЧЕНИЕ КОНСТРУКЦИИ И ИССЛЕДОВАНИЕ КОММУТАЦИОННЫХ ВОЗМОЖНОСТЕЙ МКС 26 KB
  Изучение конструкции и исследование коммутационных возможностей МКС на АТСК100 2000. Изучить конструкцию 2х и 3х позиционных МКС. Определить коммутационные возможности каждого типа МКС.
42265. ОПРЕДЕЛЕНИЕ ПОЛОЖЕНИЯ ОПТИЧЕСКОЙ ОСИ В ОДНООСНЫХ КРИСТАЛЛАХ КОНОСКОПИЧЕСКИМ МЕТОДОМ 4.42 MB
  Поэтому при изготовлении деталей необходимо знать положение оптической оси относительно рабочих поверхностей детали. Одним из методов определения ее положения является коноскопический основанный на том что в направлении оптической оси кристалла у одноосного кристалла оптическая ось совпадает с кристаллографической анизотропия оптических свойств отсутствует. Он состоит из широкого источника света S скрещенных поляризатора П и анализатора А кристаллической пластины К вырезанной перпендикулярно оптической оси кристалла и двух...
42266. ВОССТАНАВЛИВАЮЩЕЕСЯ НАПРЯЖЕНИЕ НА ПОЛЮСАХ ВЫКЛЮЧАТЕЛЯ 205.5 KB
  Эти процессы наблюдаются при трехфазном КЗ однофазном КЗ в сетях с заземленной нейтралью а также при двухфазном КЗ как в сетях высокого так и низкого напряжения. Описание установки Процессы восстановления напряжения моделируются в установке принципиальная схема которой показана на рис. В один полупериод питающего напряжения диод является проводящим и напряжение на нем практически равно нулю в другой непроводящим. Эти процессы повторяются с частотой питающего напряжения и на экране электронного осциллографа используемого для их...
42267. Планирование и организация рекламной деятельности туристского агентства Черномор Тур 177.49 KB
  Реклама - настолько сильное средство, что она может помочь продать совершенно плохой и негодный, неконкурентоспособный товар. Реклама, прежде всего, стимулирует спрос на предлагаемые товары. Механизм действия рекламы очень прост - потенциальный покупатель, услышав (увидев) о каком-либо товаре, которого у него нет, сразу захочет его купить, разумеется, при наличии денег.
42269. КООРДИНАТНАЯ АТС ТИПА АТСКУ 33.5 KB
  Основными особенностями координатных систем являются применение коммутационных блоков построенных на МКС с использованием звеньевого включения; регистровое косвенное управление; обходной способ установления соединения с применением общих управляющих устройствмаркеров. функцию управления поиском осуществляет маркер чаще всего обслуживающий всего один коммутационный блок ступени искания. В функции маркера входит определение номера входящей линии по которой поступил вызов; определение исходящей линии любой свободной или по информации...
42270. ОПРЕДЕЛЕНИЕ РАЗРЕШАЮЩЕЙ СПОСОБНОСТИ ПРИЗМ 601 KB
  По критерию Релея раздельное наблюдение изображения 1 и 2 двух близко расположенных точечных объектов возможно когда расстояние x между ними равно радиусу центрального дифракционного кружка т. В этом случае контраст элементов результирующего изображения который вычисляется по формуле .26 показан объектив в фокальной плоскости которого построены дифракционные изображения 1 и 2 с расстоянием x между ними. Использование критерия Релея приводит к контрасту результирующего изображения К=026.
42271. Прилади й методи контролю метеорологічних умов на виробництві 128.5 KB
  До показників які характеризують метеорологічні умови мікроклімат належать: температура відносна вологість швидкість руху повітря теплої випромінювання. Пояснення термінів що є в таблиці 5: Холодний період року період року який характеризуєте середньодобовою температурою зовнішнього повітря рівною 10С і нижче. Теплий період року період року який характеризуєте середньодобовою температурою зовнішнього повітря вище 10С. Вимірювання температури повітря.