Кравчук(mathcad) / 1 семестр / Excel_Теорiя

31

Операції над комірками та діапазонами комірок

Комірка (Cell)— це одиничний адресований елемент в робочому листку, який може містити значення, текст або формулу. Комірка визначається адресою, яка складається з імені стовпчика та номера рядка.

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

Вибір діапазонів. Щоб виконати над діапазоном комірок робочого листка деяку операцію, треба спочатку виділити цей діапазон. Способи виділення діапазону:

клацнути і, не відпускаючи кнопку мишки, пересунути вказівник мишки по діапазону комірок;

при натиснутій клавіші < Shift > виділити діапазон клавішами керування курсором;

натиснути клавішу < F8 > і виділити діапазон клавішами керування курсором. Для повернення клавіш керування

курсором в нормальний режим — знов натиснути клавішу

< F8 >;

виконати команду “Правка Перейти” (Edit Go To) або її клавіатурний еквівалент < F5 >, вказати адресу діапазону в діалоговому вікні “Перехід” (Go To) і клацнути на кнопці

“ОК”.

Виділення рядків та стовпчиків цілком. Нагадаємо способи, як це зробити:

клацнути на номері рядка або на букві стовпчика;

для виділення кількох суміжних рядків (стовпчиків) необхідно клацнути на номері рядка (букві стовпчика) і, не відпускаючи кнопку мишки, пересунути вказівник мишки, виділяючи потрібні рядки (стовпчики);

для виділення кількох несуміжних рядків (стовпчиків) необхідно, утримуючи натиснутою клавішу < Ctrl >, клацнути на номерах потрібних рядків (буквах потрібних стовпчиків);

для виділення стовпчика (рядка), в якому знаходиться активна

комірка натиснути комбінацію клавіш < Ctri + пропуск > (< Shift + пропуск >);

для виділення всієї таблиці — клацнути на кнопці “Виділити все” (Select All), яка розташована на перехресті заголовків

рядків	та	стовпчиків,	або	натиснути	клавіші
< Ctri + Shift + пропуск >.

32

Виділення несуміжних діапазонів. В більшості випадків виникає необхідність виділення суміжних або безперервних діапазонів. Однак в Excel є можливість працювати з несуміжними діапазонами, тобто діапазонами, що складаються з кількох прямокутних областей комірок або одиночних комірок, розташованих на відстані одна від одної. Такий діапазон ще називається кратною множиною (multiple selection). Способи

виділення несуміжних діапазонів:				< Ctri >,
*	утримуючи	натиснутою	клавішу		клацнути	на
	потрібних комірках (діапазонах);
*	виділивши	один діапазон	за допомогою клавіші < F8 >			або
	< Shift >, натиснути клавіші < Shift + F8 >, щоб вибрати ще один
	діапазон, не відміняючи виділення попереднього;

*скористатись командою “Правка Перейти” і вручну ввести в діалоговому вікні “Перехід” адреси діапазонів, відокремлюючи один від одного комою з крапкою;

*щоб виділити несуміжні діапазони, що мають однакові адреси, але розташовані на різних листках (так званий багатолистний діапазон), треба виділити діапазон на одному з потрібних листків, натиснути клавішу < Shift > і вибрати групу сусідніх робочих листків або, утримуючи натиснутою клавішу < Ctrl >,

вибрати потрібні робочі листки.

Спеціальні види виділення. Якщо для виділеного діапазону застосувати команду “Правка Перейти” і у діалоговому вікні “Перехід” клацнути на кнопці “Виділити” (Special), то з’явиться нове діалогове вікно “Виділення групи комірок ” (Go To Special). Якщо вибрати в цьому діалоговому вікні потрібні опції (потрібні признаки відбору), то Excel виділить підмножину комірок в даному діапазоні.

Примітка до комірки. Примітки (коментар) корисні для опису конкретних значень у комірках або для нагадування користувачеві, які розрахунки виконуються за формулами у комірках. Щоб додати у комірку примітку, треба її виділити та виконати команду “Вставка Примітка” (Insert Comment) або використати комбінацію клавіш < Shift + F2 >. Далі треба ввести текст примітки і клацнути в будь-якому місці робочого листка, щоб примітку сховати. Комірка з приміткою в правому верхньому кутку має червоний трикутник — індикатор примітки. Якщо помістити вказівник мишки на цю комірку, то примітка стане видимою.

Вилучення вмісту комірки виконується так: виділити комірку або діапазон і натиснути клавішу < Delete > або залучити команду

“Правка Очистити” (Edit Clear).

Копіювання комірки або діапазону. Копіювання — одна з найрозповсюдніших операцій. Існує кілька видів копіювання:

копіювання вмісту комірки в іншу комірку;

копіювання вмісту однієї комірки в діапазон комірок;

33

копіювання вмісту діапазону в інший діапазон такого ж розміру (!).

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

Процедура копіювання, як правило, складається з двох етапів:

a)виділення комірки (або діапазону комірок) для копіювання

(вихідний діапазон) і копіювання її вмісту в буфер обміну.

b)переміщення табличного курсора в комірку призначення або в лівий верхній кут діапазону, в який буде копіюватись інформація (діапазон призначення), і вставлення в нього

вмісту буфера обміну. Діапазон призначення може належати як даному робочому листку, так і іншому листку або іншій робочій книзі.

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

“Правка Відмінити вставлення” (Edit Undo Paste), або комбінацією клавіш < Ctrl + Z >, або кнопкою “Відміна” на стандартній панелі інструментів.

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

Існує кілька способів копіювання:

за допомогою кнопок панелі інструментів. На панелі інструментів “Стандартна” є кнопки “Копіювати” (Copy) і

”Вставити” (Paste). Клацання на кнопці “Копіювати”

забезпечує копіювання вмісту виділеної комірки (або діапазону

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

34

за допомогою команд меню. На першому кроці виконати команду “Правка Копіювати” (Edit Copy), а на другому

— команду “Правка Вставити” (Edit Paste).

за допомогою команд контекстного меню. Перший крок — на обраній вихідній комірці (діапазоні) клацнути правою кнопкою мишки і в контекстному меню, що з’являється, вибрати команду “Копіювати” (Copy). Другий крок — у контекстному меню активізованої комірки (виділеного діапазону) призначення вибрати команду ”Вставити” (Paste).

за допомогою клавіатурних еквівалентів. Перший крок —

< Ctrl + C >, другий крок — < Ctrl + V >.

за допомогою операції перетаскування. Вибрати вихідну комірку (діапазон), пересунути вказівник мишки на одну з її

границь (вказівник повинен перетворитись	на стрілку) і,
утримуючи натиснутими клавішу < Ctrl > і	кнопку мишки,

пересунути вказівник мишки на місце призначення (комірку або діапазон) .

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

“Правка Заповнити Вниз” (Edit Fill Down) —

копіює вміст комірки у вибраний діапазон знизу. Еквівалент — < Ctrl + D >;

“Правка Заповнити Вправо” (Edit Fill Right)

— копіює вміст комірки у вибраний діапазон справа. Еквівалент — < Ctrl + R >;

“Правка Заповнити Уверх” (Edit Fill Up) —

копіює вміст комірки у вибраний діапазон угорі;

“Правка Заповнити Вліво” (Edit Fill Left) —

копіює вміст комірки у вибраний діапазон зліва.

Ще один спосіб копіювання — перетаскування маркера заповнення вихідної комірки по виділеним коміркам призначення.

Переміщення комірки або діапазону. Операція виконується аналогічно операції копіювання, тільки на першому кроці замість команди “Копіювання” застосовується команда “Вирізати”.

Вибіркова вставка. Команда “Правка Спеціальна вставка”

(Edit Paste Special) дозволяє у комірку призначення скопіювати з буфера обміну не все підряд (значення, формати, формули, примітки та інш.), а те, що визначено опціями, встановленими у діалоговому вікні “Спеціальна

35

вставка”. Крім цього, кнопки опцій з групи “Операція” діалогового вікна “Спеціальна вставка” дозволяють виконувати математичні операції над вмістом комірок призначення та вмістом буфера обміну без використання формул. Результати операцій заносяться у комірки призначення.

Іменування комірок та діапазонів

Комірці, діапазону комірок, формулі або константі можна привласнити ім’я. В подальшому це ім’я можна використовувати в формулах.

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

Як ім’я може використовуватись набір букв, цифр і символів “_”, ”.”, ”\”, ”?”. Ім’я повинно починатись з букви, не бути схожим на адресу, не містити символів “пропуск” і складатись з не більш як 255 символів.

Існує кілька способів утворення імені:

* для вибраної комірки або діапазону застосувати команду

“Вставка Ім’я Привласнити” (Insert Name Define) і в полі

“Ім’я” діалогового вікна “Привласнювання імені” набрати надумане ім’я. Впевнитись, що в полі “Формула” знаходиться вірна адреса вибраної комірки. Далі треба клацнути на кнопці “ОК” або на кнопці “Додати” (Add), якщо є потреба іменувати ще кілька комірок. В останньому випадку необхідно в полі “Формула” ввести символ “=“ і адресу нової комірки.

Клацнути	на кнопці “ОК” і натиснути клавішу < Enter > для реєстрації
нововведених імен.
*	виділити комірку або діапазон, клацнути на полі імен, ввести

уподобане ім’я і натиснути клавішу < Enter > для реєстрації нововведеного імені.

Формули в Excel Загальні уявлення

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

36

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

Довжина формули повинна не перевищувати 1 024 символи. Якщо ввести формулу у комірку, то у комірці відобразиться результат розрахунку по цій формулі. При активізації комірки сама формула з’являється в рядку формул. Формула завжди починається із знаку “=”. По цьому признаку Excel відрізняє формулу від тексту.

Оператори

В Excel існують такі оператори:

арифметичні (“ + ”, ” - ”, ” * ”, ” / ”, ” ^ ”). Результат — числове значення.

операції над текстом (“&” — конкатенація). Результат — об’єднання двох текстових рядків.

логічні (“ = ” — логічне порівняння на дорівнює, “ > ” — логічне порівняння на більше, “ < ”— логічне порівняння на менше, ” >= ” — логічне порівняння на більше чи дорівнює, ” <= ” — логічне порівняння на менше чи дорівнює, ” < > ” — логічне порівняння на не дорівнює). Результат — значення істина (true) або хибність (false).

Перелік операторів в порядку їх пріоритету при виконанні:

I. ” ^ ” ;

II. ” * ”, ” / ” ;

III. “ + ”, ” - ” ;

IV. “&” ;

V. “ = ”, “ > ”, “ < ”, ” >= ”, ” <= ” , ” < > ”.

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

37

Вбудовані функції Excel (загальні уявлення)

Excel має велику кількість вбудованих функцій робочого листка, які можна використовувати в формулах. Сюди входять як і достатньо розповсюджені функції (СУММ (SUM), СРЗНАЧ (AVERAGE), КОРЕНЬ (SQRT)), так і функції, спеціально призначені для певних цілей, наприклад, статистичні або прикладні. Функції суттєво підвищують ефективність формул, спрощуючи їх і роблячи більш зрозумілими. В Excel є можливість створювати власні функції за допомогою VBA. Більш детально про функції Excel мова буде далі.

Введення та редагування формул

Формула повинна починатись із знаку “=”. Існують два способи введення формул — введення адрес даних вручну або вказуючи адреси використовуваних в них даних безпосередньо в робочому листку.

Введення формул вручну. Набирається знак “ = ” і сама формула. По мірі набору символи будуть з’являтись як у комірці, так і в рядку формул.

Введення формул шляхом зазначення (посилання). Зазначення стосуються адрес комірок та імен.

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

В формулі замість адреси комірки (діапазону) можна використовувати її ім’я. Імена можна набирати замість адрес або вибирати їх із списку, щоб програма Excel автоматично вставила імена у формулу. Способи вставки імен у формулу:

виконати команду “Вставка Ім’я Вставити”

(Insert Name Paste) і в діалоговому вікні “Вставка імені” (Paste Name), яке містить перелік всіх імен, визначених в даній книзі, клацнути на потрібному імені. Подвійне клацання приведе і до закриття діалогового вікна.

натиснути клавішу < F3 >, що також призведе до відкриття діалогового вікна “Вставка імені”.

Посилання на комірки (діапазони), що знаходяться за межами поточного робочого листка. В формулах можуть бути посилання на

38

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

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

< ім’я листка > ! < Адреса комірки>

Якщо в імені листка є один чи кілька пропусків, то в посиланні це ім’я розміщується в одинарних лапках —

‘< ім’я листка >‘.

*формат запису адреси комірки з іншої робочої книги —

[< ім’я робочої книги >] < ім’я листка > !< Адреса комірки > .

Якщо в імені робочої книги є один чи кілька пропусків, то в посиланні ім’я книги і ім’я листка розміщуються в одинарних лапках —

‘[< ім’я робочої книги >] < ім’я

листка >‘.

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

‘c : \ < ім’я папки > \ … \ < ім’я папки > \ [< ім’я робочої книги >] < ім’я листка >‘! < Адреса комірки >.

Абсолютні, відносні та змішані посилання. По умовчанню Excel

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

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

Увипадках, коли необхідно, щоб координати адрес комірок не змінювались, застосовується абсолютне посилання — перед буквою стовпчика і номером рядка ставиться символ “ $ ”.

Увипадках, коли необхідно зафіксувати тільки одну з координат, застосовуються змішані посилання — символ “ $ ” ставиться тільки перед тією координатою, яка неповинна змінюватись. Приклади:

	39
Адреса	Тип
A1	Відносне посилання
$A$1	Абсолютне посилання
$A1	Змішане посилання (абсолютною є координата стовпчика)
A$1	Змішане посилання (абсолютною є координата рядка)

При ручному введенні у формулі невідносних посилань можна в потрібних місцях ввести символ “ $ ” або після введення відносної адреси кілька разів натиснути клавішу < F4 >, змінюючи цим тип посилання.

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

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

двічі клацнути на комірці і відредагувати формулу безпосередньо у комірці;

виділити комірку, натиснути клавішу < F2 > і відредагувати вміст у комірці або в рядку формул;

активізувати комірку, клацнути на рядку формул і внести зміни в рядку формул;

за певними установками команд “Правка Очистити”;

виділити комірку з формулою, клацнути на кнопці “Змінити формулу” (Edit Formula) , яка знаходиться в рядку

редагування, щоб отримати доступ до засобу “Палітра формул” (Formula Palette).

Перетворення формули у значення. Якщо є формули, які завжди дають один і той же результат (так звані “мертві формули”), то можна

перетворити їх у значення. Для цього	необхідно застосувати команду
“Правка Спеціальна вставка” (Edit Paste Special), в		діалоговому
вікні “Спеціальна вставка“ (Paste	Special) встановити	перемикач

“Значення” (Values) і клацнути на кнопці “ОК”. Щоб відмінити режим вставки, треба натиснути клавішу <Enter> або <Esc>.

Режими обчислень в Excel

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

40

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

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

івідновлює їх після закінчення редагування.

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

Для встановлення ручного режиму обчислень треба через команди

“Сервіс Параметри” в діалоговому вікні “Параметри” (Options) на вкладці “Обчислення” (Calculations) встановити прапорець опції

“Вручну” (Manual).

Ручний режим передбачає обчислення за формулами після натиснення кнопки “Обчислити” на вкладці “Обчислення”. Після установки перемикача в положення вручну Excel автоматично встановлює прапорець “Перерахунок” перед збереженням.

Циклічні посилання

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

Як правило, циклічні посилання є помилковими. Якщо з’являється повідомлення про виникнення циклічного посилання, у вікні повідомлення треба клацнути на кнопці “ОК” і за допомогою панелі інструментів “Циклічні посилання” (Circular Reference), яка після цього виникає, виявити помилку.

В деяких випадках можливе усвідомлене вживання циклічного посилання. Наприклад: чистий прибуток складає доход без витрат і без відрахування, яке складає певний процент від чистого прибутку. Для, все ж таки, розв’язання такого циклічного посилання треба в діалоговому вікні

“Параметри” (Options) на вкладці “Обчислення” (Calculations)

встановити прапорець опції “Ітерації” (Iteration). Тоді повідомлення про циклічні посилання з’являтись не буде і Excel продовжуватиме обчислення до тих пір, доки результати формул перестануть змінюватись, тобто послідовність проміжних результатів зійдеться до остаточного розв’язку.