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.  Яки існують відмінності сортування від фільтрації?


 

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

24746. Логическая структуризация сети 26 KB
  Логическая структуризация сети Несмотря на появление новых дополнительных возможностей основной функцией концентраторов остается передача пакетов по общей разделяемой среде. Коллективное использование многими компьютерами общей кабельной системы в режиме разделения времени приводит к существенному снижению производительности сети при интенсивном трафике. Общая среда перестает справляться с потоком передаваемых кадров и в сети возникает очередь компьютеров ожидающих доступа. Это явление характерно для всех технологий использующих разделяемые...
24747. Функции маршрутизатора в сети 26.5 KB
  Функции маршрутизатора в сети Маршрутиза́тор сетевое устройство пересылающее пакеты данных между различными сегментами сети и принимающее решения на основании информации о топологии сети и определённых правил заданных администратором. Нередко маршрутизатор используется для обеспечения доступа из локальной сети в глобальную сеть Интернет осуществляя функции трансляции адресов и межсетевого экрана.
24748. Функции шлюза в сети 23.5 KB
  Функции шлюза в сети Сетевой шлюз аппаратный маршрутизатор или программное обеспечение для сопряжения компьютерных сетей использующих разные протоколы например локальной и глобальной. Сетевой шлюз может быть специальным аппаратным роутером или программным обеспечением установленным на обычный сервер или персональный компьютер.
24749. Многоуровневый подход к разработке средств сетевого взаимодействия 22.5 KB
  Многоуровневый подход к разработке средств сетевого взаимодействия В компьютерных сетях идеологической основой стандартизации является многоуровневый подход к разработке средств сетевого взаимодействия. Именно на основе этого подхода была разработана стандартная семиуровневая модель взаимодействия открытых систем ставшая своего рода универсальным языком сетевых специалистов.
24750. Модель взаимодействия открытых систем (модель OSI) 32 KB
  Модель взаимодействия открытых систем модель OSI Международная организации по стандартизации придумала и создала Модель OSI модель взаимодействия открытых систем. Модель OSI Тип данных Уровень Функции Данные 7.
24751. Функции физического уровня модели OSI 33.5 KB
  Функции физического уровня модели OSI Функции всех уровней модели OSI могут быть отнесены к одной из двух групп: либо к функциям зависящим от конкретной технической реализации сети либо к функциям ориентированным на работу с приложениями. Модель OSI представляет хотя и очень важную но только одну из многих моделей коммуникаций. Модель OSI Open System Interconnection описывает взаимосвязи открытых систем. Модель OSI Тип данных Уровень Функции Данные 7.
24752. Функции канального уровня модели OSI 33.5 KB
  Функции канального уровня модели OSI Функции протоколов канального уровня различаются в зависимости от того предназначен ли данный протокол для передачи информации в локальных или в глобальных сетях. Протоколы канального уровня используемых в локальных сетях ориентируются на использование разделяемых между компьютерами сети сред передачи данных. К таким типовым топологиям поддерживаемым протоколами канального уровня локальных сетей относятся общая шина кольцо и звезда. Примерами протоколов канального уровня для локальных сетей являются...
24753. Адресация компьютеров в сети Интернет 14.71 KB
  Числовой составной адрес IPадрес2. Символьный адрес доменное имя. Каждый из множества ПК входящих в Интернет имеет свой собственный УНИКАЛЬНЫЙ адрес. Это числовой адрес IPадрес: IP Internet Protocol IPадрес состоит из четырех групп цифр например 194.
24754. Символьные адреса 14.79 KB
  Символьные адреса Каждый из множества ПК входящих в Интернет имеет свой собственный уникальный адрес. Это числовой адрес IPадрес: IP Internet Protocol IPадрес состоит из четырех групп цифр например 194. Этот адрес неудобен для человека поэтому IPадресам поставлены в соответствие символьные адреса доменные имена. Служба которая обеспечивает преобразование символьного адреса доменного имени в числовой IPадрес называется службой доменных имен DNS DomainName Service.