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

План

Лекція №7

Тема: Обчислення в Excel

Мета:

§ Формувати вміння проведення розрахунків в електронних таблицях, пояснити правила створення формул та використання функцій в Excel.

• Розвивати логічне мислення, память, увагу, спостережливість, вміння робити висновки.
• Виховувати старанність,наполегливість, вміння зосереджуватись.

1. Створення формул

2. Використання функцій

3. Копіювання клітинок і формул

4. Арифметичні операції та операції з текстом

5. Логічні функції

6. Практичне завдання: моделювання облікових і економічних табличних розрахунків за допомогою Microsoft Excel

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

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

Рис. 1

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

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

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

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

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

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

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

Рис. 2

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

Рис. 3

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

Рис.4

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

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

Рис5

Копіювання клітинок і формул.

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

Рис.6

основний вигляд курсору Excel;

курсор копіювання (заповнення)

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

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

Рис. 7

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

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

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

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

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

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

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

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

# ДЕЛ/0! — означає ділення на нуль. Як правило, за дільник взято пусту клітину;

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

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

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

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

Дата добавления: 2014-01-07