Excel_teor1-2
.pdfКривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
над значеннями дужок. Причому, математичні дії виконуються за у такій послідовності (пріоритетом): спочатку здійснюється піднесення до степеня, потім множення та ділення, і, нарешті, додавання та віднімання. Якщо пріоритет дій однаковий (множення і ділення; додавання і віднімання), то дії виконуються в порядку їх запису у формулі. Наприклад, значення формули =8/2*5-(10-2*3) після обчислення дорівнює 16.
Досить часто зустрічаються формули, що містять стандартні функції
Excel. Розглянемо назви та призначення деяких функцій Excel.
Математичні функції: SIN – обчислення синуса числа; COS – косинус числа; TAN – обчислення тангенс числа; КОРЕНЬ – арифметичний корінь числа. Наприклад, формула =SIN(A1) обчислює синус числа, яке записано в комірці А1. Це число є радіанною мірою кута.
Статистичні: СУММ – обчислення суми значень комірок певного діапазону; МИН (МАКС) – знаходження мінімального (максимального) значення в певному діапазоні; СРЗНАЧ – обчислення середнього значення. Наприклад, формула =СРЗНАЧ(B1:B5) обчислює середнє значення п’яти чисел, які містяться в діапазоні комірок B1:B5.
Логічні: Аргументами логічних функцій є логічні вирази. Логічний вираз – це вираз, який складається зі знаків “=”, “>”, “<”, “>=” (більше або дорівнює), “<=” (менше або дорівнює), “<>” (не дорівнює) та може набувати лише значення “істина” (ИСТИНА) або “хиба” (ЛОЖЬ). Наприклад, в програмі Excel допустимі такі логічні вирази: 5<12 (істина), A1>=7, 2*С3<>D8, “а”<“б” (істина), “а”<“б” (істина), “а”=“А” (істина), “п'ять”<”дванадцять” (істина), “мороз”<“морозиво” (істина).
Найчастіше з логічних функцій використовується функція ЕСЛИ (логічне “якщо”). Розглянемо синтаксис цієї функції:
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)
де лог_выражение - це логічний вираз або функція, яка повертає значення ИСТИНА або ЛОЖЬ.
значение_если_истина - це значення, яке повертає функція ЕСЛИ, якщо лог_выражение дорівнює ИСТИНА.
значение_если_ложь - це значення, яке повертає функція ЕСЛИ, лог_выражение дорівнює ЛОЖЬ.
Тобто, ця функція повертає одне із двох значень в залежності від значення логічного виразу.
Також до логічних функцій відносяться: И – повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення ЛОЖЬ. ИЛИ – повертає значення ИСТИНА, якщо хоча б один аргумент має значення ИСТИНА; повертає значення ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ. НЕ – змінює на протилежне значення свого аргументу: повертає ИСТИН, якщо аргумент ЛОЖЬ, та навпаки.
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
При обчисленні значення формули, що містить функції, спочатку обчислюється значення функції, а потім обчислюється значення всієї формули за описаним вище правилом.
Програма Excel допускає вкладення функцій, тобто в якості параметра однієї функції може бути вказано значення іншої функції або вираз, який містить функцію. Наприклад, у формулі =КОРЕНЬ(SIN(A2)^2+COS(A3)^4) аргумент функції КОРЕНЬ є виразом, що містить функції SIN та COS.
Комірки, які містять обчислювальні значення, прийнято називати похідними. Всі інші комірки називають основними. Для перегляду залежностей між основними та похідними комірками
При зміні значення в основній комірці автоматично перераховуються значення в похідних комірках. На малопотужних комп’ютерах при роботі з великими таблицями це призводить до уповільнення роботи. В таких випадках, використовуючи команду Сервис → Параметры → Вычисления, бажано встановити параметр „Вычисления вручную”. При встановленні цього параметру обчислення (перерахунок значень похідних комірок) здійснюються лише після натискання клавіши <F9>.
Повідомлення про помилки у формулах
Якщо при створенні формули допущена помилка, то в комірці відображається не результат обчислення, а повідомлення про зроблену помилку. В залежності від виду помилки повідомлення можуть бути такими:
#ССЫЛКА – це повідомлення вказує на те, що формула містить посилання на неіснуючу (можливо, знищену) комірку;
#ДЕЛ/0! – ділення на нуль при обчисленні формули;
#ЧИСЛО! – неправильне встановлення числових аргументів функції або формула повертає числове значення, яке занадто велике або занадто мале, щоб його можна було представити в Excel. Наприклад, аргументом функції КОРЕНЬ вказано від’ємне число.
#ИМЯ? – при введенні імені функції або адреси комірки допущена помилка;
#ПУСТО! – з’являється у випадку, якщо задано перетин двох областей, які в дійсності не мають спільних комірок;
#Н/Д – аргументом функції виступає посилання на порожню комірку (неповні дані);
#ЗНАЧ! – аргументом функції є величина недопустимого типу. Порада: Для усунення деяких помилок у формулах, а також для
перегляду зв’язку між даними в таблиці, можна використовувати команду Сервис → Зависимости формул.
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
Створення формул
Будь-яку формулу можна ввести за допомогою клавіатури. Але більш зручнішим є створення формули з використанням мишки.
рис. 2.2
Як відомо, формула починається зі знаку “=”. Для введення цього символу використовується відповідна клавіша на клавіатурі. Для введення числових значень, дужок та знаків математичних операцій використовується клавіатура. Для введення посилання на іншу комірку досить при створенні
формули клацнути мишкою по цій |
|
|||||
комірці. В результаті цього комірка |
|
|||||
позначається |
рамкою зі |
штриховою |
|
|||
лінією (рис. 2.2, комірка В1) та у |
|
|||||
формулу |
автоматично |
вводиться |
|
|||
посилання на цю комірку. |
|
|
||||
Для |
завершення |
введення |
|
|||
формули потрібно клацнути по кнопці |
|
|||||
“Enter” в рядку формул або аналогічній |
|
|||||
клавіші. |
|
|
|
|
|
|
Якщо формула містить функції, то |
|
|||||
для |
введення |
функції |
зручно |
|
||
використовувати поле “Функции” рядка |
рис. 2.3 |
|||||
формул. |
Дане поле з’являється на місці |
|||||
|
||||||
поля “Имя” під час створення формули і відображає назву функції, яка використовувалась останньою (на рис. 2.2 це функція SIN). Для вибору функції, при створенні формули, натискають кнопку 
та мишкою вибирають одну з 10 функцій, які використовувались останніми (рис. 2.3). Якщо потрібної функції у даному списку немає, то треба вибрати пункт “Другие функции…”, а далі вибрати функцію з повного переліку функцій Excel. Після вибору деякої функції на панелі формул відображається “бланк функції”, який містить ім’я функції, поля для введення значень аргументів, опис функції та аргументів, а також значення функції, при умові, що введені аргументи (рис. 2.4).
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
рис. 2.4
Для введення аргументів функції, які найчастіше всього є посиланнями на комірку або діапазон комірок, зручно використовувати мишку. Для цього спочатку потрібно мінімізувати панель формул, натиснувши на кнопку 
, що знаходиться справа від поля для введення відповідного аргументу. Вибрати мишкою потрібну комірку або діапазон комірок та розгорнути панель формул, натиснувши на кнопку 
. Якщо є потреба введення декількох аргументів функції, то потрібно почергово мінімізовувати панель формул натискуючи на кнопки мінімізації для введення відповідних аргументів. Якщо формула закінчується функцією, то після введення аргументів функції для завершення введення формули досить натиснути кнопку “Ok” на панелі формул. У випадку необхідності продовження введення формули потрібно поставити текстовий курсор в рядку формул та продовжити введення формули.
Також для створення формули, яка починається з функції, можна використовувати кнопку „Вставка функции” 
рядка формул. При натисканні на цю кнопку в комірку вводиться знак „=” та з’являється вікно „Мастер функций”, за допомогою якого можна вибрати функцію та ввести її аргументи.
Для редагування формули потрібно поставити текстовий курсор в рядку формул та зробити необхідні зміни. Якщо текстовий курсор в рядку формул поставити біля назви функції, то після натискання кнопки “Вставка
функции” 
з’явиться бланк відповідної функції, в якому зручно змінювати аргументи функції.
Особливості копіювання формул з абсолютними та відносними адресами комірок
Формули можна копіювати та переміщувати використовуючи стандартні методи та автозаповнення. При автозаповненні формулами потрібно виконати ті ж самі дії, що й для автозаповнення повторюваними
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
даними, тобто виконати протягування лівою кнопкою миші маркера заповнення.
При переміщенні формула та результат не змінюються. А результат копіювання та автозаповнення залежить від адресації, яка використовується в посиланнях на комірки: абсолютна, відносна чи комбінова. За замовчуванням посилання на комірки у формулах створюються з відносною адресацією. Наприклад, посилання А1 є відносним.
При відносній адресації адреси комірок, які використовуються в формулах, визначаються відносно місця розташування формули. Цей принцип призводить до того, що при будь-якому копіюванні формули в іншу комірку змінюються адреси комірок в формулі. Наприклад, формулу в комірці C1 (=A1+B1) (рис. 2.5), у якій обидва посилання є відносними, можна прочитати так: додати значення комірок, розташованих на одну і на дві комірки лівіше від даної. При копіюванні даної формули в інші комірки вона приймає такий вигляд: в C2 =A2+B2; в D1 =B1+C1; D2 =B2+C2 і т.д.
Відповідно, різними будуть і результати обчислень за цими формулами.
рис. 2.5
У випадках коли необхідно відмінити дію принципу відносної адресації, використовується прийом, який називається заморожуванням адреси. Для цієї цілі в адресі комірки використовується символ $. Для заморожування всієї адреси знак $ ставиться двічі. Наприклад, $A$1. Ця адресація називається абсолютною. При абсолютній адресації копіювання формули не змінює адресу. Наприклад, якщо формулу =$A$1+$B$1 скопіювати в інші комірки, то вона залишиться такою ж самою. Відповідно не зміниться і результат. Цю формулу можна прочитати так: додати значення комірок А1 та В1.
Адреси у формулі можуть бути комбінованими, тобто може бути заморожений окремо стовпчик або рядок. Наприклад, формула в комірці C1 може мати такий вигляд =$A1+B$1. Ця формула читається так: додати значення комірки стовпчика А і рядка, в якому записана формула, до значення комірки, що знаходиться в стовпчику на один лівіше від даного в рядку 1. При копіюванні буде змінюватися та частина, яка є відносною, тобто перед якої не стоїть знак $ (рис. 2.6).
рис. 2.6
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
Якщо при введенні або редагуванні формули курсор знаходиться одразу після адреси деякої комірки, то можна вибрати один із чотирьох можливих варіантів адресації, послідовно натискуючи клавішу F4.
Посилання на комірки інших аркушів та інших книг
Формули Excel можуть мати посилання на комірки інших робочих аркушів. Такі формули використовують у випадку, коли таблиці з даними знаходяться на різних аркушах. Посилання має такий вигляд: <ім’я аркуша>!<адреса>. Наприклад, посилання у формулі на комірку С2 аркуша “Зразок” буде мати такий вигляд: Зразок!C2.
|
Іноді використовують посилання на комірки іншої книги Excel. У |
|||||
цьому |
випадку |
посилання |
буде |
мати |
такий |
вигляд: |
[<ім’я книги>]<ім’я аркуша>!<адреса>. Наприклад, |
посилання |
у формулі |
||||
на комірку С2 аркуша “Зразок” книги “Книга1.xls” буде мати такий вигляд: [Книга1.xls]Зразок!С2.
Формули з наведеними посиланнями створюються аналогічно звичайним формулам. При цьому, для створення посилання на інший аркуш потрібно спочатку перейти на інший аркуша, виконавши клацання по ярлику аркуша, а потім клацнути потрібну комірку. При створенні посилання на комірку з іншої книги спочатку потрібно перейти до даної книги (наприклад, використовуючи меню “Окно”, якщо книга відкрита), а потім вибрати потрібну комірку.
Імена комірок та діапазонів та їх використання у формулах
Крім звичайного імені типу А1 комірці можна надати спеціальне ім’я. Наприклад, комірці А2 (рис. 1.1) можна присвоїти ім’я “перше”, а комірці В2 – “друге”. Тоді формулу в комірці С2 можна записати так: =перше+друге. Хоча можна записати так як і раніше: =А2+В2. Результат від цього не зміниться.
При копіюванні формули з посиланням у вигляді спеціального імені це посилання розглядається як абсолютне.
Також спеціальні імена можна задавати діапазонам комірок. Для надання імен коміркам та діапазонам і їх вставки у формули використовують команду Вставка → Имя. Більш зручним є використання поля “Имя” рядка формул. У цьому випадку для надання імені комірці або діапазону спочатку їх виділяють, а потім вводять ім’я в це поле і натискають клавішу <Enter>. Для введення у формулу посилання на комірку зі спеціальним ім’ям досить клацнути по цій комірці або ввести ім’я комірки з клавіатури.
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
Діаграми
Поняття про діаграми та їх створення
Для графічного подання даних в Excel використовують діаграми. Під терміном діаграма об’єднують різноманітні типи графіків, діаграм, гістограм, які створюються на основі даних, що записані в комірках робочого аркуша.
В Excel можна створити більше 30 різновидів діаграм. Але основою для цих діаграм є три види: кругова діаграма, гістограма та лінійний графік.
Кругова діаграма показує співвідношення між цілим і його частинами. На круговій діаграм відображаються співвідношення даних,
які розміщені у одному рядку або стовпчику (рис. 2.7а).
Рис. 2.7б. Приклад діаграми: гістограма
Рис. 2.7в. Приклад діаграми: графік
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
Діаграми Excel складаються з певних елементів. Кожен елемент має своє призначення і може зустрічатися в різних видах діаграм. На рис. 2.8 наведено приклад гістограми з елементами, які найчастіше зустрічаються в різноманітних діаграмах. Ця гістограма побудована за даними робочого
Рис. 2.9. Діапазон даних для побудови гістограми
аркуша, які відображені на рис. 2.9.
Для побудови діаграми використовують Майстер діаграм, який викликають командою Вставка→ Диаграмма... Процес створення діаграми за
Вісь |
|
|
|
Назва |
|
значень |
|
|
|
діаграми |
|
|
|
|
|
|
|
|
|
|
|
Область |
|
Мітки |
|||||
|
|
|
побудови |
||
даних |
|
|
|
||
|
|
|
діаграми |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Легенда |
|
|
|
|
|
|
|
|
|
|
|
|
|
Шкала, |
|
|
|
|
|
мітки |
|
|
|
|
|
поділок |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Вісь |
|
Точка |
|
Заголовок |
|
Область |
||
категорій |
|
даних |
|
осі Ох |
|
діаграми |
||
Рис. 2.8. Основні елементи діаграми
допомогою Майстра складається з таких чотирьох кроків:
1)вибір типу діаграми;
2)зміна або вибір діапазону даних, на основі яких буде побудовано діаграму, і визначення способу формування її рядів;
3)зовнішнє оформлення діаграми: заголовків, осей; ліній сітки, легенди, підписів даних, таблиці;
4)вибір варіанта розташування діаграми: на поточному або на окремому аркуші.
Зауваження: якщо вибрати діапазон даних, на основі яких буде побудовано діаграму, до початку побудови діаграми, то при створенні
діаграми на першому кроці Майстра можна проглянути зразок діаграми, а на другому кроці при необхідності лише змінити деякі параметри. Також у цей діапазон даних можна включати заголовки даних.
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
Приклад побудови діаграми
Розглянемо побудову діаграми, яка зображена на рис. 2.8, на основі даних рис. 2.9, для графічного порівняння цін на системні блоки та монітори у різних фірмах. Ці дані розташовані на аркуші “аналіз_цін”. Для кращого аналізу побудови діаграми перед побудовою діаграми нічого не виділятимемо.
1). Дамо команду Вставка → Диаграмма… . Внаслідок чого з’явиться діалогове вікно “Мастер диаграм”, в якому виберемо тип “Обычная гистограмма” (рис. 2.10а). Для переходу до наступного кроку натиснемо кнопку <Далее>.
2). На другому кроці побудови діаграми на вкладці “Диапазон данных” в поле “Диапазон” введемо діапазон даних для побудови діаграми. А саме, введемо діапазон, що містить тільки числові дані (=аналіз_цін!$B$2:$E$3) (рис. 2.10б). У разі введення діапазону комірок, що містить підписи (=аналіз_цін!$A$1:$E$3), Excel ідентифікує текстові дані як підписи осі Ох та назви рядів даних, що є одним з можливих варіантів побудови діаграми. Також на цій вкладці переконаємо, що встановлений перемикач “ряды в строках”, тобто будуємо діаграму по двох рядах даних “Сист. блок” та “Монітор”.
Кривонос О.М., Шимон О.М. Вивчення Excel на фіз.-мат. спеціальностях (ver 0.1)
рис. 2.10а
Зауваження: Для побудови діаграми по несуміжним рядам даних для виділення цих рядів здійснюється протягування миші при натисненій клавіші
<Ctrl>.
На вкладці “Ряд” заповнюємо змінюємо стандартні імена рядів “Ряд1” та “Ряд2” на “Сист.блок” та “Монітор”, використовуючи поле “Имя”, а також задаємо підписи осі Ох у відповідному полі (рис. 2.10в).