1.      Формули та функції Excel.
  2.      Сортування та фільтрація даних в Excel
  3.      Побудова графіків, діаграм Excel.

 Формули та функції Excel

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

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

Наприклад, якщо в клітинку буде введено вираз =55+30, то замість буде відображено число 85. Первісний вираз, який ви ввели, можна побачити й відредагувати в рядку формул.

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

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

В Excel визначені такі арифметичні операції:

+ — додавання;

– — віднімання;

* — множення;

/ — ділення;

^ — піднесення до степеня.

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

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

Приклад: В клітинки А1, А2, А3, А4, А5 напишемо будь-які цифри, а в клітину А6 напишемо формулу = А1+А2+А3+А4+А5 i натиснемо Enter. Excel  підрахує суму і запише в клітину А6.

Між іншим суму можна одержати, використавши стардатну функцію Excel.

Виділимо клітини з А1 по А5 і натиснемо  кнопку Автосумма  на панелі інструментів і відразу одержимо суму чисел.

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

Формула автоматично відображається в стрічці формул. Якщо ми використаємо кнопку Автосумма на панелі інструментів то в клітині  А6 буде функція =СУММ(А1:А5).

Excel  може визначати суму по всьому стовпчику: тоді слід записати =СУММ(А: А);  по рядку =СУММ(1:1), але зверніть увагу, що  формули слід ставити в інший стовпчик і в інший рядок, щоб не було циклічних додавань.

Розглянемо приклад, коли потрібно додати стовпчики А, С, а В пропустити. Тоді формула  матиме вигляд =СУММ(А1:А6; С1:С6).

Іноді виникає необхідність в формулі використати дані з іншої таблиці на іншому листі, наприклад,  знайдемо суму, що складається з вмісту клітин А1 на першому та другому аркушах і розмістимо цю формулу в клітині А1 на тертьому аркуші, формула матиме вигляд: =СУММ(Лист1!A1;Лист2!A1).

Якщо виділити мишею прямокутну ділянку, наприклад п’ять стрічок в стовпчиках А, В, С і натиснути кнопку Автосумма, обчислиться сума по кожному з виділених стовпчиків.

Якщо таким самим способом потрібно одержати суму по стрічках, то слід виділити діапазон А1:С5 і захопити пустий стовпчик D, де і розмістяться суми обчислені по стрічках.

І аналогічно, якщо потрібно обчислити суму даних, які знаходяться на ділянці виділених клітин по стовпчиках і рядках, потрібно виділити пустий рядок і пусту стрічку і Ехсеl обчислить суму по стовпчиках і рядках.

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

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

Цей чорний хрестик служить для копіювання інформації, що є в клітинах, в т.ч. і формул; при цьому формули копіюються таким чином, що аргументи змінюють свої адреси в напрямку руху курсора. Якщо формула була =А1+В1, то при при переміщенні вниз  формула змінює свій вигляд на =А2+В2 і т.д. Аналогічно веде себе Excel і при копіюванні формул по стрічці. Наприклад якщо в А6 є функція = СУММ(А1:А5), то копіюючи формулу по рядку одержимо =СУММ(В1:В5) і т.д. Це значно економить час при моделюванні розрахунків, особливо з складними, повторюваними формулами. Також можна скопіювати формулу з однієї  клітини в іншу, якщо вони розташовані не поряд. Для цього потрібно зробити активною клітину з формулою, поставити курсор на її горизонтальну або вертикальну границю (курсор перетвориться на об’ємну білу стрілку), натиснути Сtrl, тоді біля стрілки з’явиться значок “плюс”, який буде означати, що ми робимо копіювання, тепер можна перетягти формулу в потрібне місце. Якщо не натискати Ctrl, то формула переміститься на нове місце. Таким самим чином можна переміщувати і  копіювати стовпчики, стрічки, діапазони клітин.

Розглянемо як можна забло-кувати зміну адреси клітини при копіюванні формул. Наприклад, гривні потрібно перевести в долари по курсу. Суму в гривнях помістимо в колонку А, а курс у клітину С1, суму в доларах в колонку С. Як же заблокувати зміну адреси клітини С1 при копіюванні? Формула в клітині С4 буде мати вигляд =$С$1*А4, копіюємо її в клітини С5:С7, і побачимо, що адреса клітини, яка містить курс, не змінилась. Знак $ блокує зміну адреси клітини, тобто ми задали для клітини С1 в формулі абсолютну адресу.

Блокування клітини здійснюється натисканням клавіші F4, або введенням знака $ з клавіатури.

При необхідності можна задати абсолютну адресу стовпчика або стрічки. Є ще інший спосіб адресації – шляхом присвоєння імені клітини комбінацією клавіш Ctrl +F4. Потім можна звертатися  до потрібної клітини з  будь-якого аркуша книги.

Арифметичні операції і операції з текстом. Excel  використовує звичайні знаки арифметичних операцій +, — ,* , /, ^. Запис арифметичних операцій здійснюється за загальноприйнятими правилами, можна використовувати багаторівневі дужки. Наприклад: =((F4+C5)/E6)*(B2-D2)+C4^2. Щоб об’єднувати або приєднувати текст до формул використовують знак & (амперсанд). Наприклад, обчислена сума виражається в грн., тоді формула набирає вигляд: =(F4+C5)/E6)*(B2-D2)+C4^2&”грн”. Зверніть увагу: приєднаний текст береться в лапки. Пробіли також повинні знаходитись в лапках. Дуже важливо знати, що цифра з назвою не вважається числом і не може використовуватися як аргумент в інших формулах. В Excel можна об’єднувати текст. Наприклад, в клітину А1 введемо текст Наш головний партнер, в А2 – масло, в А3 – завод. В А4 напишемо формулу = “В 2001 році “&A1“є”& A2&А3, одержимо “В 2001 році наш головний партнер є маслозавод”.

Логічні  функції значно розширюють можливості  Excel:

команда ЕСЛИ дозволяє організовувати різного виду розгалуження. Формат команди: = ЕСЛИ (Логічна умова; коли вірно; коли не вірно). Логічна умова може бути виражена знаками >, <, =, >=, <=,< >. Наприклад:  = Если (С1>D1*B5; “УРА!”; “НА ЖАЛЬ…”. Вираз означає, якщо в клітині С1 число більше, ніж добуток D1*B5, то в нашій клітині відобразиться текст УРА!, інакше в клітині буде текст НА ЖАЛЬ…

команда И дозволяє задати декілька умов, які можна використовувати в команді  ЕСЛИ. Всі умови в команді И повинні бути виконані, тільки тоді функція приймає значення ІСТИНА. Якщо хоч одна умова не  виконана, то її значення ЛОЖЬ. Формат команди : = И (логічна умова 1; логічна умова 2). Всього логічних умов може бути до 30. Так, наприклад, разом можуть виконуватися  ЕСЛИ і И. =Если (И (Е1>1; C2 = “УРА!”); “Угадав”; “Не угадав”) – якщо в клітині Е1 знаходиться число >1, а в С2 слово “УРА!”, то в нашій клітині висвітиться “Угадав” (Істина), якщо ж будь-яка з логічних умов не виконана (ЛОЖЬ), в нашій клітині одержимо “Не угадав”.

Команда  ИЛИ також дозволяє задати декілька умов, але іншим способом. Якщо хоча б одна з умов  виконується, то функція набирає значення  Істина; коли всі значення умови не виконані одержимо значення Ложь. Формат команди: =ИЛИ (логічна умова1, логічна умова2), як і формат И, використовуватися може з ЕСЛИ. Наприклад: = ЕСЛИ (ИЛИ В1>=1; D2=“ТАК”) “ПРЕМІЯ”; “НЕМАЄ ПРЕМІЇ”) – якщо в клітині В1 коефіцієнт >= 1 або в клітині присутній текст “ТАК” в нашій клітині висвітлиться  “ПРЕМІЯ”, якщо ні одна з умов не виконана, в клітині висвітлиться “ПРЕМІЇ НЕМАЄ”.

Види помилок. Різні види помилок розкривають причини з яких нас не розуміє Excel:

# ДЕЛ/0! – Означає ділення на нуль – звичайно це за дільник вибрана пуста клітина.

# ИМЯ? – Невірно набрано ім’я клітини в формулі. Наприклад, замість латинських літер використані українські.

# ЗНАЧ! – Замість числа в формулі використано текст.

# ССЫЛКА! – Клітинка,  на яку робилося посилання була видалена командою Правка/Удалить, коли відбувалося  зсув  клітинок по стрічці і стовпчику.

#ЧИСЛО! – Спроба добути квадратний корінь з від’ємного числа, результат обчислень надто великий, або малий щоб його можна відобразити в Excel.

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

— сортування по зростанню.

—  сортування по спаданню.

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

Сортування даних по декількох полях. Засоби Excel дозволяють одночасно сортувати записи по трьох полях. Послідовність сортування полів вибирається в діалоговому вікні Сортировка диапазона в списках, що розкриваються: Сортувати по, Потім по, В останню чергу, по.

Розташовані поруч із кожним списком перемикачі по зростанню, no зменшенню дозволяють задати напрямок сортування.

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

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

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

виділити таблицю;

скористатись командою Данные/Фільтр/Автофильтр. Відразу в кожній клітині верхньої стрічки з’явиться по кнопці списка.

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

 

Побудова графіків, діаграм Excel Excel володіє широкими можливостями для побудови діаграм  різних типов та видів.

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

Як правило, на діаграмі відображається послідовність значень певного параметра залежно від значень аргументів. Послідовність значень параметра в Excel 2003 називають рядом даних, а послідовність значень аргументів – категорією.

Для наведеної таблиці, кількість рядів даних і категорій залежить від того, як розміщені дані. Якщо вони розміщені по рядках, то рядів даних – 7 (Озима пшениця, Озиме жито, Ячмінь, Овес, Гречка, Картопля, Овочі), а категорій – 2 (Площа, га, Структура, %).  Діаграма має ряд елементів, які наведені в таблиці.

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

Далі потрібно скористатися майстром діаграм, який створює діаграму в загальному випадку за чотири кроки (для деяких типів діаграм кількість кроків може бути меншою). Після натискання кнопки Мастер диаграмм на панелі інструментів Стан¬дартная на екрані з’явиться вікно діалогу Мастер диаграмм – шаг 1 из 4.

У цьому вікні слід вибрати тип діаграми (вибрана діаграма зображується інверсним кольором) і її вид. Для переходу до наступного кроку натискаємо кнопку Далее.

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

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

У вікні діалогу Мастер диаграмм – шаг 3 из 4 виводиться зразок діаграми. На основі аналізу виділеного діапазону майстер сам визначає, як розміщені ряди даних.

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

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

В вікні діалогу Мастер диаграмм – шаг 4 из 4 вказуємо місце розміщення діаграми – на окремому листі чи на активному. Після натискан¬ая кнопки Готово цього вікна діаграма переноситься на вказаний лист. На кожному кроці роботи майстра можна повернутися на крок назад (кнопка Назад) або припинити побудову діаграми (кнопка Отмена).

Редагування діаграм. Excel має засоби для редагування елементів побудованих діаграм. Для редагування діаграм елемент необхідно виділити. Для виділення елемента діаграми слід активізувати діаграму, клацнувши мишею по полю діаграми, а потім клацнути по елементу.

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

Питання для самостійної перевірки знань:

  1. Що таке формула Excel?
  2. Як побудувати формулу у ЕТ Excel?
  3. Чим різняться абсолютні і відносні посилання на клітини у формулах Excel?
  4. Як перевести відносне посилання на клітину у абсолютне?
  5. Чи можна при побудові формули використовувати дані з інших листів ЕТ?
  6. Що таке функція Excel? Які функції ви знаєте?
  7. Опишіть порядок роботи з майстром функцій.
  8. Що таке сортування інформації? Як виконується сортування інформації в ЕТ Excel?
  9. Що таке фільтрація інформації? Як виконується фільтрація інформації в ЕТ Excel?

10. Як на основі табличних данних побудувати графік?