Формування списку

Таблиця 8.2

В разі підрахунку суми реалізації використаний інший прийом для того, щоб можна було правильно копіювати форму-
ли: для посилання на роздрібну ціну конкретного товару ви-
користовується частково абсолютна адреса: I$2 — у цій адресі заборонена зміна номера рядка, тому в разі копіювання та-
кої формули для товару «банани» помилок не виникне. Для інших товарів потрібно створити формули з посиланням на їх роздрібну ціну.

8.1.4.4. Функції

Поняття функції

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

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

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

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

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

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

Типи функцій

Для зручності роботи функції в Excel розбиті по категоріях: функції керування базами даних і списками, функції дати і часу, DDE/Зовнішні функції, інженерні функції, фінансові, інформаційні, логічні, функції перегляду і посилань. Крім того, присутні такі категорії функцій: статистичні, текстові та ма­тематичні.

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

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

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

У Excel широко представлені математичні функції. Наприклад, можна виконувати різні операції з матрицями: множити, знаходити зворотну, транспонувати.

За допомогою статистичних функцій можливо проводити статистичне моделювання. Крім того, можливо використовувати елементи факторного та регресійного аналізу.

У Excel можна розв’язувати задачі оптимізації та використовувати аналіз Фур’є. Зокрема, в Excel реалізований алгоритм швидкого перетворення Фур’є, за допомогою якого ви можете побудувати амплітудний і фазовий спектри.

8.1.4.5. Майстер функцій

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

Рис. 8.17. Майстер функцій

Майстер функцій викликається командою Вставка/Функции чи натисканням на кнопкиМастер функций. Ця кнопка розташована на панелі інструментів Стандартна, а також у рядку формул.

Розглянемо застосування функції СУММ(діапазон) (сума діапазону комірок) і СРЗНАЧ(діапазон) (середнє значення діапазону комірок) для підбиття проміжних підсумків, на прикладі табл. 8.3.

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

Такий вигляд матиме результат обчислення формул (табл. 8.4).

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

Таблиця 8.3

	A	B	C	D	E	F	G	H	I
	№ п/п	Найменування	Фірма-поста- чальник	ціна, грн	ціна, $	В	Сума закупівлі	Сума реалізац.	Валовий дохід
		Банани	Frutis		=D6/kurs		=D6*F6	=F6*I$2	=H6-G6
		Банани	SUMP	2.32	=D7/kurs		=D7*F7	=F7*I$2	=H7-G7
		Банани	Forum	1.98	=D8/kurs		=D8*F8	=F8*I$2	=H8-G8
9		Банани	Like	1.86	=D9/kurs		=D9*F9	=F9*I$2	=H9-G9
		Банани	UFO	2.05	=D10/kurs		=D10*F10	=F10*I$2	=H10-G10
			Середн:	=СРЗНАЧ (D6:D10)	Разом:	=СУМ (F6:F10)	=СУМ (G6:G10)	=СУМ (H6:H10)	=СУМ (I6:I10)
		Виноград	Frutis		=D12/kurs		=D12*F12	=F12*I$3	=H12-G12
25		Разом:			Разом:	=СУМ (F6:F22)	=СУМ (G6:G22)	=СУМ (H6:H22)	=СУМ (I6:I22)

Таблиця 8.4

	Сьогодні курс $=	1.3				Роздрібна ціна
							Банани	2.90 грн
	Розрахунок валового доходу від реалізації товарів	Виноград	5.20 грн
							Ананас	6.80 грн
№ п/п	Наймену- вання	Фірма- постачальник	ціна, грн	ціна, $	Кількість	Сума закупівлі	Сума реалізації	Валовий дохід
	Банани	Frutis	2.00 грн	$ 1.54		400.00 грн	580.00 грн	180.00 грн
	Банани	SUMP	2.32 грн	$ 1.78		185.60 грн	232.00 грн	46.40 грн
	Банани	Forum	1.98 грн	$ 1.52		326.70 грн	478.50 грн	151.80 грн
4	Банани	Like	1.86 грн	$ 1.43		595.20 грн	928.00 грн	332.80 грн
	Банани	UFO	2.05 грн	$ 1.58		246.00 грн	348.00 грн	102.00 грн
Банани	Середня:	2.04 грн	Разом:		1 753.50 грн	2 566.50 грн	813.00 грн
	ананаси	Like	5.99 грн	$ 4.61		359.40 грн	408.00 грн	48.60 грн
	ананаси	UFO	6.60 грн	$ 5.08		363.00 грн	374.00 грн	11.00 грн
Ананаси	Средн:	6.04 грн	Підсумок:		1 861.55 грн	2 142.00 грн	280.45 грн
	Разом:			Підсумок:		4 865.02 грн	6 356.90 грн	1 491.88 грн

8.1.5. Серії та діаграми

8.1.5.1. Серії

В Excel розроблений механізм уведення рядів даних (серій). Серії — це ряди даних, побудовані за певним законом. При цьому дані не обов’язково мають бути числовими, вони можуть бути і формалізованими текстовими.

Створювати серії можна в кілька способів, окремим підрозділом створення серій є Прогресія.

Прогресія

Для побудови довільних серій виконайте команду Прав­ка/Заполнить/Прогрессия. З’явиться вікно діалогу Прогрессия, в якому можна встановити параметри серій.

Рис. 8.18. Створення Прогресії

У вікні можна вибрати тип прогресії — Арифметическая, Геометрическая, Даты й Автозаполнение—і як вона буде заповнюватися По строкам чи По столбцам.

Длястворенняпрогресії необхідно ввести в комірку початкове значення, вибрати Расположение і Тип прогресії, вказати Шаг і Предельное значение, а також натиснути OK.

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

Автозаполнение. У цьому режимі можна продовжувати різні типи даних. Можливість Автозаполнения логічно продовжує деякі задані послідовності, наприклад, Кв.3, Кв.4, Кв.5. Ви можете використовувати можливість Автозаполнения безпосередньо за допомогою переміщення курсора заповнення на робочому аркуші чи за допомогою вікна діалогу Прогрессия.

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

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

Створення серій

Крім прогресії, серії можна створювати ще кількома способами без допомоги меню. Вони наведені нижче.

Перший спосіб

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

Рис. 8.19. Перший спосіб створення серій

Такий простий процес уведення можливий лише тоді, коли в комірку введений один з елементів наявного списку. Списки створюються в діалоговому вікні Сервис/Параметры на закладці Списки.

Рис. 8.20. Другий спосіб створення серій

Другий спосіб

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

Даний спосіб є найзручнішим для створення простих серій (типу арифметичної прогресії).

Рис. 8.21. Третій спосіб створення серій

Третій спосіб

Третій спосіб є найуніверсальнішим для створення серій будь-якого цифрового типу арифметичної та геометричної прогресії, статистичні ряди й інші складні серії.

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

Як уже говорилося вище, третій спосіб є найуніверсальнішим.

8.1.5.2. Діаграми та графіки

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

Типи діаграм

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

Рис. 8.22. Типи діаграм

Побудова діаграм

Для побудови діаграми виконаємо команду Вставка/ Диаграммачи натиснемо кнопку Мастер диаграмм .

Мітками рядків і стовпців називаються заголовки рядків і стовпців. Якщо ви не включаєте мітки рядків у ділянку побудови діаграми, то на 4-му кроці побудови діаграми потрібно вказати, що під мітки рядків виділяється 0 рядків.

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

XY-крапкова діаграма є найпридатнішим типом діаграми для обробки результатів лабораторних досліджень.

Лінії тренда можна проводити на гістограмах, графіках, лінійчатих і XY-крапкових діаграмах.

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

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

Розглянемо побудову діаграм на прикладі даних, наведених у табл. 8.3 і 8.4. Будь-яка діаграма може будуватися на підставі таблиць. Побудуємо кругову діаграму «Валового доходу». Кругова діаграма будується на одному ряді даних, значення елементів ряду відображаються як сектори діаграми (величина сектора залежить від величини елемента ряду). В нашому прикладі потрібно побудувати кругову діаграму по валовому доходу. Причому ряд даних має містити всі цифри доходу, за винятком загальної суми. Така діаграма буде заготовкою для відображення діаграми, що динамічно змінюється залежно від установленого на таблиці фільтра.

Процедура створення діаграми.

· Запустити Майстер діаграм .

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

· У першому кроці майстра вказати діапазони комірок: $B$5:$C$23; $I$5:$I$23, але краще виділити даний діапазон заздалегідь, тоді він автоматично вказуватиметься в запиті на введення діапазону комірок[2].

· На наступних кроках вибирається круговий тип діаграми та її підтип.

Рис. 8.23. Підтипи кругової діаграми

· Потім установлюються параметри, що визначають: яка частина діапазону буде відображена як дані, а яка — як мітки сегментів і назви.

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

Excel 7.0/95

· На наступних кроках вибирається круговий тип діаграми та її підтип.

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

Рис. 8.24. Визначення міток

Excel 8.0/97

· Виділити стовпець таблиці «Валовий дохід» (включаючи назву стовпця).

· Запустити Майстер діаграм.

· На 1-му кроці майстра вибрати круговий тип діаграми.

· На 2-му кроці майстра вибрати закладку Ряд і встановити такі значення в 3-х полях уведення: «Имя» — адреса комірки, що містить текст «Валовий дохід» (уже повинен стояти), «Значение» — адреса блоку комірок зі значеннями валового доходу (вже має стояти), «Подписи категорий» — адреси комірок таблиці з найменуваннями товарів і фірм-постачальників (це поле введення потрібно заповнити мишею, використовуючи кнопку згортання майстра наприкінці поля. По закінченні заповнення знову натиснути цю кнопку для повернення до вікна майстра).

Рис. 8.25. Крок 2

· На третьому кроці — вибрати закладку Подписи данных і встановити перемикач у положення доля.

Рис. 8.26. Крок 3

· Останній крок майстра дає змогу вибрати один із варіантів розташування графіка: на окремому аркуші робочої книги (новому) чи на поточному аркуші.

Діаграма з нашого прикладу — це ніби заготовка для відображення різної інформації залежно від установлюваного фільтра чи включення рівня структури документа. Наприклад, якщо встановити фільтр по товарі «Стіл», одержимо такий вигляд діаграми:

Рис. 8.27. Результат побудови діаграм

Редагування діаграм

Для редагування діаграми.

· Двічі натисніть мишею в будь-якому місці діаграми.

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

· Для форматування об’єкта діаграми натисніть на ньому праву кнопку миші і виберіть потрібну для форматування команду зі списку, що з’явився.

· Для заміни одного ряду даних у діаграмі іншим застосуйте команду Формат ряда.

· Змінюючи лінії графіка, можна змінити дані на робочому аркуші.

· Команда Тип диаграммдає можливість змінювати тип уже існуючої діаграми.

· Команда Автоформатзмінює не лише тип діаграми, а й встановлює стандартні параметри діаграми.

· Команда Объемный видзмінює просторову орієнтацію діаграми.

Під час створення діаграми змішаного типу виникають дві групи рядів. Одна група — гістограма, а друга може бути графіком чи з ділянками XY-крапкової. Після створення діаграми ви можете використовувати для кожного ряду даних будь-який тип плоскої діаграми.

Ви можете змінити формат діаграми так, що Excel будує за замовчуванням.

Команда Автоформатстворює користувацький автоформат для побудови діаграм і графіків. Для зміни формату побудови стандартних діаграм у вікні діалогу Параметры виконайте команду Сервис/Параметрыі виберіть вкладку Диаграмма.

8.1.6. Робота зі списками та бази даних[3] у Excel

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

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

В Excel бази даних розміщуються в таблицях. Кожна таблиця складається з рядків і стовпців, що у базах даних називаються, відповідно, записами і полями.

Інформація в базах даних має постійну структуру. Кожен рядок можна розглядати як одиничний запис. Інформація в межах кожного запису міститься в полях.

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

· На робочому аркуші можна обробляти кілька списків, але все-таки краще помістити кожен список на окремий аркуш робочої книги.

· Засоби обробки списків Excel можна використовувати, не створюючи унікальні заголовки стовпців, але все-таки це краще зробити.

Щодо термінів база даних і список, то це фактично синоніми.
І все ж дотримуватимемося того, що прийнято в Microsoft, — називатимемо таблиці в документах Excel списками. Базою даних називатимуться файли таблиць — створені іншими системами обробки даних, такими, як Microsoft Access, dBase чи Microsoft FoxPro.

Ефективність списку обумовлена такими характе-
ристиками.

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

· Один чи два верхні рядки у списку мають містити мітки, що описують призначення відповідного стовпця.

· Необхідно уникати порожніх рядків і стовпців усередині списку.

· В ідеалі на робочому аркуші не повинно бути нічого, крім списку. Якщо це неможливо, то список потрібно відокремити від інших даних принаймні одним порожнім рядком і одним порожнім стовпцем.

· Якщо планується фільтрувати список, не вміщуйте якісь дані на рядки, в яких він розташований.

Щоб підкреслити заголовки, скористайтеся вкладками Шрифт або Рамка діалогового вікна Формат ячеек. (Воно відкривається за командою Формат/Ячейки.) Не вставляйте окремий рядок з дефісів чи знаків рівності, тому що Excel може витлумачити таке підкреслення як дані.

Нові дані можна додавати безпосередньо в кінець списку. Дуже зручним може виявитися порядкове редагування спис-
ку за допомогою стандартної екранної форми. Для цього ви-
діліть якусь комірку в списку та виконайте команду Данные/Форма.

Примітка.Перед виконанням команди Данные/Форма повинна бути виділена тільки одна комірка в списку, інакше у формі можуть з’явитися неправильні заголовки стовпців.

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