Лекція 11. Тема 2.4.Формули і функції MSExcel

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

Формули електронної таблиці

Кожна формула завжди починається зі знака «=». Далі вносять послідовність символів — алгоритм роз­рахунку. Результатом роботи формули є значення, яке виводиться як результат розрахунку поданих даних. Якщо змінити ці дані, то автоматично перераховується і результат.

При створенні формул і функцій використовують такі елементи:

a. арифметичні оператори: (") — піднесення до степеня; (*) — множення, (/) — ділення; (+) — додавання, (-) — віднімання; (%) — відсоток від числа;

b. оператори порівняння: =, >, <, >=, <=, <> (не дорівнює);

c. текстовий оператор конкатенації & (амперсанд) — об'єднання кількох послідовностей символів в одну;

d. оператори посилань:

: (двокрапка) — оператор діапазону комірок (напри­клад, А1:С4);

;(крапка з комою) — об'єднання кількох діапазонів комірок (наприклад, СУММ(А1:С4; E1:F4)).

За наявності у формулі послідовності кількох типів операторів вони виконуються відповідно до таких пріо­ритетів:

1) %;

2) ^;

3) *, /;

4) &;

5) =; <>; <=; >=; <>/

Змінюють порядок проведення розрахунків за допо­могою вставляння дужок у формулу.

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

Сума кредиту: Термін кредиту.

Для цього прикладу формула для розрахунку повин­на міститися у стовпці G(«Сума щомісячного погашен­ня кредиту»). Щоб її створити, активізують комірку G3, із клавіатури вводять значок „=», і, клацаючи кла­вішею миші на комірці потрібного стовпця у рядку З, вводять такий вираз =B3/D3 і натискають на клавішу Enter(Ввод). У комірці G3 з'являється розраховане значення, а на панелі формул (позиція 7 на рис. 2.1) — формула, відповідно до якої виконували розрахунок.

Далі формулу необхідно внести в кожну комірку діапазону G3:G9. Для цього створену в комірці G3 фор­мулу копіюють у всьому стовпцю: покажчик миші під­водять до правого нижнього кутка робочого вікна (покажчик перетворюється на «+»), натискають на кла­вішу миші й, не відпускаючи її, «тягнуть» формулу вздовж стовпця. Формула копіюється, автоматично змінюються адреси комірок. Адресацію, коли відбу­вається автоматична зміна адрес комірок при копіюван­ні, називають відносною.

За абсолютної адресації адреси комірок при копію­ванні не змінюються. Для створення такого типу адре­сації додають символ $ до адреси комірки: щоб не змі­нювалися назви стовпців, його вводять перед назвою; щоб не змінювалися номери рядків — перед номером рядка. Наприклад, внесені в таблиці суми кредитів у гривні необхідно представити відповідно до курсу євро. З огляду на те що курс валют може змінюватися досить часто, його значення доцільно вмістити в окрему комір­ку, а у формулах для розрахунку використовувати посилання на неї. На рис. 2.16. виведено таблицю, де у стовпці А — значення сум кредиту у гривні, комірка С2 містить поточний курс євро, а стовпець В — перерахова­ні у євро суми кредиту. Для зручності абсолютну адресу створюють так: вводять потрібну адресу комірки і нати­скають на клавішу F4.

Рис. 2.16.. Використання абсолютної адресації в розрахунках

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

Використання імен у формулах

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

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

Наприклад, формула =Оклад+Премія значно доступ­ніша для з'ясування суті розрахунку, ніж формула =А2+С2.

Існують певні обмеження при створенні імен. Пер­шим символом в імені повинна бути літера, знак під­креслення або зворотний слеш («\»), довжина — до 255 символів. Не можна використовувати імена анало­гічні системним, наприклад R1C1, а також пробіли. Для розділення кількох слів послуговуються або зна­ком підкреслення, або крапкою.

Для створення імені окремої комірки або виділеного діапазону комірок виконують такі дії:

5) виділяють комірку або діапазон комірок;

6) установлюють курсор у полі імені (позиція 3 на рис. 2.1);

7) вводять потрібне ім'я;

8) натискають на клавішу Enter(Ввод).

Також можна задати ім'я за допомогою команд. Наприклад: стовпець «Сума кредиту, грн» має ім'я «Сума_кредиту», стовпець «Сума комісії за видачу кре­диту, грн» — ім'я «Сума комісії». Необхідно розраху­вати суми значень двох діапазонів для кожного рядка. Спочатку виділяють комірку або діапазон комірок. Далі виконують такі дії:

1. обирають команду Присвоить имя в групі Определенные имена на вкладці Формули;

2. у вікні Создание имени, що з'являється на екрані дисплея (рис. 2.17.), у полі Имя вводять потрібне ім'я («Сума кредиту»);

Рис. 2.17. Присвоєння імені діапазону комірок

3. у полі Область вводять діапазон дії імені: це може бути визначення всього файла (як у цьому прикладі) або ім'я певного робочого листка;

4. у полі Диапазон перевіряють діапазон комірок, який встановлюється автоматично відповідно до діапа­зону попередньо виділених комірок;

5. натискають на кнопку ОК.

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

=Сума_комісії+Сума_кредиту.

При створенні формули у комірку вводять знак «=» та першу літеру імені, наприклад =су, після чого на екрані дисплею з'являється перелік імен та функцій, серед якого обирають потрібне (рис. 2.18.).

Рис. 2.18. Перелік імен та функцій

Для вилучення створених імен активізують кнопку Диспетчер имен в групі Определенные имена на вклад­ці Формулы. У вікні, що з'являється на екрані дисплею (рис. 2.19.), виділяють потрібне ім'я (на екрані — «Сума_кредиту»), натискають на кнопку Удалить.

Рис. 2.19. Вилучення створених імен

Списки значень

У MS Excel2007 для спрощення вводу інформації існує можливість створити список допустимих зна­чень, які використовують для підстановки. Для цього спочатку в рядку або стовпці вводять потрібні значен­ня без пустих комірок (наприклад, діапазон АЗ:А9 на рис. 5.1.). Відтак виділяють діапазон комірок для вве­дення значень (це може бути стовпець, рядок, діапа­зон комірок поточного або іншого робочого листка). Наприклад, виділяють стовпець С робочого листка Лист6, на вкладці Данньїе в групі Работа с данньїми обирають команду Проверка данньїх (на рис. 2.20.).

У вікні, що з'являється на екрані дисплея, відкри­вають вкладку Параметри (рис. 2.21.). У полі Тип данньїх: із переліку вибирають Список, у полі Источник вводять діапазон комірок, який використовуєть­ся для підстановки (АЗ:А9). Натискають на кнопку ОК.

Рис. 2.20. Активізація команди Проверкаданньїх

Рис. 2.21.. Створення списку, який розкривається

При активізації будь-якої комірки стовпця С ліво­руч від неї з'являється кнопка-список (рис. 2.22.).

Рис. 2.22.. Кнопка-список

Розкривши кнопку-список, можна вставити потріб­не значення.

Робота з функціями

Для зручності користувачів усі стандартні функції MSExcelвміщено у групи — категорії. Існують кате­горії: «Математические», «Статистические», «Финан-совьіе», «Текстовьіе». Аргументи функції відокремлю­ються від назви круглими дужками та між собою сим­волом «;». Аргументами функцій є числа, текст, логічні значення, масиви, посилання. Деякі функції не мають аргументів, наприклад функція СЕГОДНЯ() або ПИ().

Авторозрахунок

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

1. за допомогою кнопки Автосумма, яка розташо­вана у вкладці Формули, в групі Библиотека функций;

2. за допомогою кнопки Сумма, що вміщена в групі Редактирование вкладки Главная (рис. 2.23.).

Рис. 2.23.. Активізація авторозрахунку

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

— відкривають перелік функцій кнопки Автосумма (рис.2.24.) і вибирають потрібну;

Рис. 2.24. Вибір функції із переліку кнопки Автосумма

— клацають правою клавішею миші у рядку статусу на значенні функції, яка з'являється автоматично (позиція 7 на рис. 2.1), із переліку функцій вибирають потрібну та натискають кнопку Enter.

Використовувати такі методи можна, розраховуючи значення як рядків, так і стовпців. Для стовпців активі­зують вільну комірку під стовпцем, для рядків — віль­ну комірку праворуч від рядка.

Лекція12

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

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

1) натиснути на кнопку Вставить функцію , яка розташована у вкладці Формульї, у групі Библиотека функций;

2) активізувати кнопку Вставить функцію яка вміщена на панелі формул (позиція 4 на рис. 2.1).

Наприклад, користуючись даними таблиці, пода­ної на рис. 2.1, необхідно знайти максимальне значен­ня сум кредитів. Тоді активізують комірку Allі вікно Мастерфункций, показане на рис. 2.25. Цю саму дію можна виконати за допомогою комбінації клавіш Shift + F3;

У вікні Мастер функций поле Поиск функции (1)

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

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

У списку Категория (2) містяться категорії, в яких функції згруповано за певною ознакою (наприклад, математичні, статистичні тощо), категорія Полньїй алфавитньїй перечень охоплює всі функції, категорія 10 недавно использовавшихся — функції, які викори­стовувались востаннє.

У полі Вьіберите функцию (3) вказано функції, що вміщені у вибраній категорії.

Для поданого прикладу вибирають функцію МАКС, активізують кнопку ОК, після чого на екрані дисплея з'являється вікно, показане на рис. 2.26.

Рис. 2.26.. Використання функції МАКС у розрахунках

У цьому вікні в полі Числої вказують діапазон зна­чень комірок, які розраховують, у розглянутому при­кладі — це діапазон ВЗ:В9. Його вводять із клавіатури або визначають автоматично, натиснувши на клавішу миші та провівши нею по відповідних значеннях табли­ці (ВЗ:В9). У вікні можна побачити результат розрахун­ків (позиція 1 на рис. 2.26.). Якщо розрахунок прово­дять для кількох діапазонів, то заповнюють поля Число2, ЧислоЗ тощо. Додаткову інформацію за вибра­ною функцією можна переглянути після активізації гіперпосилання Справка по зтойфункции (позиція 2 на рис. 2.26.) Після активізації кнопки ОК результат з'яв­ляється у комірці таблиці, в якій установлено курсор (A11).

Аргументами функцій можуть бути також числа і вбудовані функції.

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

§ активізують потрібну комірку, наприклад на Лист 6 (комірка А1);

§ із клавіатури вводять символ «=»;

§ за допомогою інструмента Мастерфункций акти­візують функцію МАКС;

§ переходять на робочий листок «Платежі» із початковими значеннями;

§ при натиснутій клавіші миші задають необхідний діапазон комірок за суми кредитів (ВЗ:В9);

§ у вікні функції МАКС натискають на кнопку ОК.

· На робочому листку Лист6 з'являються результати розрахунку, а створені формули можна переглянути у рядку формул (рис. 2.27.). Функція містить посилання на діапазон комірок іншого робочого листка і має вигляд: =МАКС(Платежі!ВЗ:В9).

Для розрахунку функції можна використати й такий спосіб. У комірку вводять знак «=» та першу літе­ру функції, наприклад =С (рис. 2.28.).

Рис. 2.27. Створення формул із посиланням на комірки іншого робочого листка

Рис 2.28. Перелік функцій потрібних для розрахунку.

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

Функції СЧЕТЕСЛИ, СУММЕСЛИ

Для обчислення результату розрахунку значень, які задовольняють умову, використовують функції СЧЕТЕСЛИ, СУММЕСЛИ.

Функція СУММЕСЛИ. Наприклад, необхідно роз­рахувати суму кредитів у банку «Аваль». Для цього спочатку активізують вільну комірку таблиці та вікно Мастерфункций, у переліку знаходять функцію СУММЕСЛИ. Аргумент Диапазон містить значення діапазону комірок, серед яких відшукуються ті, що задовольняють поставлену умову (назви банків (АЗ:А9)). В аргумент Критерий вносять значення умови з клавіатури (у прикладі — «Аваль») або активі­зують будь-яку комірку, що містить потрібну назву (наприклад, А4).

У цей аргумент також можна ввести число, умовний вираз, текстове значення, адресу комірки.

Аргумент Диапазон_суммирования містить діапа­зон комірок, у якому відбувається підсумовування; при цьому опрацьовуються тільки ті записи, значення яких задовольняють поставлену умову. Для поданого при­кладу це діапазон із значеннями сум кредитів (ВЗ:В9). Можна не вносити значення в Диапазон_суммирова- ния, тоді опрацьовується діапазон комірок, адреси яких задано в аргументі Диапазон.

Унаслідок виконуваних дій з'являється вікно, зображене на рис. 2.29.

Рис. 2.29. Використання функции СУММЕСЛИ

Функція СЧЕТЕСЛИ. З її допомогою розраховують кількість комірок діапазону, які задовольняють поста­влену умову. Якщо, наприклад, слід розрахувати кіль­кість значень поля «Сума комісії за видачу кредиту», що перевищують 200, то ця функція матиме вигляд = C4ETECJIH(F3:F9;«>200»), а результатом розрахун­ку буде 3.

Логічні функції ЕСЛИ, И, ИЛИ, НЕ

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

До логічних функцій належать ЕСЛИ, ЕСЛИ- ОШИБКА, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

Функція ЕСЛИ. Її використовують для розрахунку одного з кількох виразів залежно від виконання поста­вленої умови. У загальному вигляді функція має такі аргументи:

ЕСЛИ (Лог_вьіражение; Значение_если_истина; Значение_ес ли_л ожь).

Якщо задана умова після розрахунку має значення ИСТИНА, то розраховується значення аргументу Зна-чение_если_истина, якщо значення умови після розра­хунку буде ЛОЖЬ — значення аргументу Значение_ если_ложь. При цьому аргументи можуть мати вигляд вбудованої функції ЕСЛИ. У разі складних перевірок їх буває до 64.

Наприклад, треба розрахувати функцію такого вигляду:

де х = -1; 4; 9; -5; 16; -2,5.

Спочатку на новому робочому листку створюють таблицю, яка містить значення X у діапазоні комірок А2:А7. Далі для розрахунку першого значення Y уста­новлюють курсор у комірку В2 й активізують вікно Мастер функций, знаходять функцію ЕСЛИ, натиска­ють на кнопку ОК. З'являється вікно, зображене на рис. 2.30.

У вікні послідовно вносять потрібні значення:

§ в аргументі Лог_вьіражение ( 2) створюють першу умову (X < 0), причому X в таблицях MS Excel— це адреса комірки, що містить значення X. Умова матиме вигляд А2 < 0;

§ аргумент Значение_если_истина містить розра­хунок першого випадку, якщо умова аргументу Лог_вьіражение має значення ИСТИНА. У першому виразі є вбудована функція модуля числа. Для її внесен­ня необхідно активізувати список функцій, який розта­шований у лівій частині панелі формул (3) і вибрати команду Другие функции.

Рис.2.30.. Внесення значень в аргументи функції ЕСЛИ

Знайшовши функцію розрахунку модуля (ABS), заносять значення в аргумент (А2) й активізують кноп­ку ОК. Після цього Мастерфункций закінчує свою роботу.

Щоб продовжити створення складної функції й забезпечити надалі її коригування, слід активізувати кнопку Вставить функцию панелі формул (1). Після занесення значень цей аргумент матиме такий вигляд: ABS(A2)+1.

Аргумент Значение_если_ложь містить розрахунок другого виразу, якщо умова аргументу Логическоевьіражение має значення ЛОЖЬ. У цьому разі необхід­но активізувати вбудовану функцію ЕСЛИ зі списку функцій (3) на панелі формул, внести потрібні значен­ня, після чого аргумент буде такий:

ЕСЛИ(А2>1;А2^{;^}1/2)/2;0).

Функцію створено, далі її копіюють для всіх зна­чень Y (за допомогою знака «+» в нижньому правому кутку комірки). У результаті функція ЕСЛИ набуває вигляду, показаного на рис. 2.31.

Аналогічно можна розв'язати задачу відповідно до поставленої умови і для інших випадків. Наприклад, на основі даних табл. 5.1 необхідно провести розрахунок: банк «Аваль» зменшує комісію за кредит у 1,2 раза. Для розрахунку використовують функцію ЕСЛИ з такими аргументами:

=ЕСЛИ(АЗ=«Аваль»;ЕЗ/1,2;ЕЗ),

копіюють її для всього діапазону рядків і одержують розраховані значення:

1,00%, 1,04%, 1,50%, 1,46%, 1,46%, 1,75%, 2%.

Рис. 2.31. Розрахунок функції ЕСЛИ

Функція И. Її використовують для об'єднання двох і більше умов. Наприклад, для перевірки, чи належить X (комірка А2 на рис. 2.31.) до діапазону від 2 до 5, використовують функцію И з такими аргументами:

И(А2>2;А2<5).

Результатом розрахунку є ЛОЖЬ (рис. 2.32.).

Рис. 2.32.. Використання функції И в розрахунках

Функція ИЛИ. Ця функція перевіряє введені зна­чення і повертає значення ИСТИНА, якщо хоча б один із аргументів має значення ИСТИНА. Наприклад, щобперевірити, чи належить X (комірка А2 на рис. 2.31.) до діапазону менше 0 або дорівнює 10, функція має вигляд =ИЛИ(А2<0;А2=10) і повертає значення ИСТИНА.

Функція НЕ. Вона змінює значення свого аргументу на протилежне. Її використовують для точного визна­чення значення, яке не може бути отримано. Напри­клад, усі значення X, окрім «-1», необхідно збільшити удвічі. Для цього, користуючись значеннями, подани­ми на рис. 5.35, в комірці С2 створюють функцію тако­го виду: =ЕСЛИ(НЕ(А2=-1);А2*2) і копіюють її в діапа­зоні С2:С7.

Функції дати і часу

Для роботи із значеннями типу Дата і Время в табличному процесорі MSExcelіснують спеціальні функції, зокрема ДАТАЗНАЧ (перетворення дати з тек­стового формату на числовий), ЧИСТРАБДНИ (роз­раховує кількість робочих днів між двома датами), ГОД (повертає дату числового формату в вигляді року) та ін. Усі вони вміщені в категорії Дата и время інструмента Мастерфункций. Якщо функції немає в переліку, необхідно додати компоненти MSExcel. Для цього вико­нують такі дії:

- активізують значок (Кнопка «Office») та кнопку Параметри Excel;

- у наступному вікні вибирають категорію Надстройки, в полі Управление значення Надстройки Excel та клацають клавішею миші на кнопці Перейти;

- у вікні, що з'являється на екрані дисплея (рис. 2.33.), активізують перемикачі ліворуч від потріб­ного компонента (в цьому випадку — Пакет Анализа).

Проілюструвати роботу з деякими функціями можна на конкретних прикладах функцій ДАТА, ДЕНЬ, ДЕНЬНЕД, СЕГОДНЯ.

Функція ДАТА. Вона повертає значення дати. їх загальний вигляд такий: ДАТА(рік;місяць;день).

Наприклад, функція ДАТА(2000;2;1) залежно від установленого формату дати повертає значення 01.02.00.

Рис. 2.33. Активізація додаткових компонентів MS Excel

Існує, наприклад, таблиця даних, де в стовпці В містяться значення років, у стовпці С значення міся­ців, у стовпці Dднів. Функція для розрахунку дати має вигляд: =ДАТА (B2;C2;D2). Під час перетворення зна­чення з формату дати на загальний або числовий функ­ція повертає числове значення дати. Так, значення функції ДАТА(2001;1;2) дорівнює 36 893.

Функція ДЕНЬ. Ця функція повертає день дати в числовому форматі. Наприклад, у комірці F2 вміщена дата 28.10.2003, тоді значення функції ДЕНЬ(Е2) дорів­нює 28.

Функція ДЕНЬНЕД. Вона має загальний вигляд ДЕНЬНЕД(дата,тип) і повертає порядковий номер дня тижня поданої дати. При цьому аргумент тип визначає порядок розрахунку і може мати значення:

a. (за замовчуванням) — число від 1 (неділя) до 7;

b. — число від 1 (понеділок) до 7;

c. — число від 0 (неділя) до 6.

Наприклад, функція =ДЕНЬНЕД(Е2) (в F2 — дата 28.10.2003) повертає значення 3, а функція =ДЕНЬНЕД(«23.10.2003»;2) — значення 2.

Функція СЕГОДНЯ. Вона має загальний вигляд СЕГОДНЯ() і повертає значення поточної дати.

Створення і робота з функціями користувача

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

· клацають клавішею миші на значку (Кнопка Office) та кнопці Параметрьі Excel;

· у лівій частині вікна Параметри Excel обирають групу Основние;

· у правій частині вікна в групі Основние параме­три работи с Excel активізують перемикач Показивать вкладку «Разработчик» на ленте.

Для створення функції користувача на вкладці Раз­работчик у групі Код активізують кнопку (VisualBasic). Після цього відкривається вікно Microsoft Visu­alBasic, у якому вибирають команди Insert, Module. Активізується вікно Code (рис. 2.34.), в якому з клавіа­тури вводять такі команди:

FunctionРентабельність (Прибуток, Активи) Рентабельність = Прибуток / Активи * 100 EndFunction

Рис. 2.34. Створення функції користувача

Існує можливість створити текстовий коментар до функції аналогічний тому, який є в стандартних функ­ціях MSExcel. Для цього на вкладці Разработчик угрупі Код активізують кнопку шкрош (Макроси). Увікні Макрос (рис.2.34.) у полі Имя Макроса ( 1) вводять ім'я створеної функції («Рентабельність») та натиска­ють на кнопку Параметри (2).

Рис. 2.34. Створення коментаря до функції користувача

Далі у вікні Параметри макроса у полі Описание вводять текстовий коментар (рис. 2.35.).

Рис. 2.35. Текстовий коментар до функції користувача

Для роботи зі створеною функцією в таблиці активі­зують потрібну комірку і за допомогою інструмента Мастерфункций вибирають категорію Определенньїепользователем і функцію Рентабельність. Далі вносять відповідні значення в аргументи функції (рис. 2.36.).

Рис. 2.36. Розрахунок функції користувача Рентабельність

Робота з масивами значень

Однією з функцій Excelє робота з масивами зна­чень.

Масив — сукупність елементів одного типу, упорядкованих таким

чином, що їх опис однозначно визначає місцезнаходження кож­ного елемента.

У таблицях Excel дії з елементами масиву слід вико­нувати у певній послідовності:

виділяють діапазон вільних комірок, який має стільки ж рядків і стовпців, як і початковий;

у рядку формул вводять знак «=»;

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

у рядку формул вводять потрібну формулу й активізують комбінацію клавіш Ctrl+ Shift+ Enter; розраховують значення кожного елемента нового маси­ву. При цьому створена формула автоматично береться у фігурні дужки, наприклад {=А2:А6 + 10}, які засвід­чують, що це формула масиву.

Формули в комірках розрахованого масиву змінюва­ти не можна, спочатку необхідно виділити весь масив. Значенням масиву можна також надати ім'я, яке й використовують надалі у формулах. Для цього діапазон потрібних значень виділяють, активізують команду Присвоитьимя групи Определенньїе імена вкладки Формули, вводять потрібне ім'я (наприклад, «Масив») і натискають на кнопку ОК. Тоді введена вище формула матиме такий вигляд: {=Масив + 10}.

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

Матриця — прямокутна таблиця чисел, яка містить mрядків і nстовпців.

Матриці можна перемножувати між собою, множи­ти на вектор, транспонувати, створювати обернені матриці тощо.

Спочатку створюють матрицю А у діапазоні комірок А2:С4 (рис. 2.37.), наприклад

A=

Рис 2.37. Розраховані значення функцій для роботи з матрицями

Розраховані значення функцій для роботи з матрицями

З елементами матриці можна виконувати такі дії.

1) множення матриці на число. Якщо необхідно помножити цю матрицю на число 3, виділяють область вільних комірок за розміром, що відповідає заданій матриці (наприклад, D2:F4). Далі у рядку формул вводять формулу =А2:С4*3 й активізуютьклавіші Ctrl+ Shift+ Enter. У виділеному діапазоні комірок з'являються значення початкових даних, помножені на 3;

2) множення матриці на вектор. Для цього викори­стовують функцію МУМНОЖ (Массиві; Массив2), де Массиві, Массив2 — це масиви, які перемножуються. Кількість стовпців аргументу Массиві має дорівнювати кількості рядків аргументу Массив2. Унаслідок мно­ження матриць утворюється масив з такою самою кіль­кістю рядків, що й масив 1, і з такою самою кількістю стовпців, що й у масиві 2. Множення матриць здійсню­ють за формулою:

де b _ij— елемент нового масиву; і — номер рядка; j — номер стовпця; а_n — елемент масиву 1; c_kj — елемент масиву 2; k — номер рядка масиву 1 або номер стовпця масиву 2; n— розмір матриці.

При множенні матриці на вектор розрахована матриця має розмірність вектора. Спочатку створю­ють вектор у діапазоні G2:G4, який повинен мати стільки рядків, скільки стовпців у матриці. Далі виді­ляють діапазон комірок, де буде створюватися нова матриця (А7:А9), активізують кнопку Вставка функции, вибирають функцію МУМНОЖ і вносять від­повідні значення діапазонів матриці А та вектора С (рис. 2.38.).

Рис. 2.38.. Використання функції МУМНОЖ

Установлюють курсор на рядок формул, активізу­ють клавіші Ctrl + Shift + Enter й одержують:

MУMHOЖ (A2:C4;G2:G4);

3) створення обернених матриць. Такі матриці застосовують для розв'язання систем рівнянь з кілько­ма невідомими. Для цього активізують функцію МОБР, що виконує обчислення з точністю до 16-значних чисел (для округлення значень слід користуватися функцією ОКРУГЛ).

Наприклад, потрібно створити обернену матрицю з елементів матриці А. Для цього виділяють діапазон комірок (D7:F9), де буде створюватися нова матриця, активізують кнопку Вставка функции, вибирають функцію МОБР і заносять відповідні значення елемен­тів матриці А:

МОБР (А2:С4).

Установлюють курсор на рядок формул і натискають на клавіші Ctrl+ Shift+ Enter;

4) транспонування матриць. Для цього використо­вують функцію ТРАНСП, яка переводить вертикальний діапазон комірок у горизонтальний, і навпаки. Напри­клад, необхідно створити транспоновану матрицю з еле­ментів матриці А. З цією метою виділяють діапазон комірок, де буде створюватися нова матриця (А12:С14), активізують кнопку Вставка функции, вибирають функцію ТРАНСП і вносять відповідні значення еле­ментів матриці А:

ТРАНСП (А2:С4).

Установлюють курсор на рядок формул й активізу­ють клавіші Ctrl+ Shift+ Enter.

Щоб перевірити, чи правильний розрахунок, пе­ремножують матрицю А на обернену матрицю, вико­ристовуючи функцію МУМНОЖ (А2:С4;Б7:Е9). Унас­лідок цього отримують матрицю з елементами 1 по діа­гоналі.

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

Рис. 2.39. Перегляд розрахованих функцій

Функції посилання та пошуку

До групи функцій посилання та пошуку належать функції АДРЕС, ВПР, ИНДЕКС, ПРОСМОТР, СТРОКА

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

Наприклад, на робочому листку «Довідник» існують два довідники (рис. 2.40). У першому довіднику вміще­но інформацію за трьома замовленнями, у другому — форми оплати.

Рис. 2.40. Таблиця довідників

На робочому листку «Таблиця» введено значення замовлених виробів та функції пошуку (рис.2.41.). необхідно вивести в таблиці форми оплати та фірму замовника по кожному виробу.

Ці та інші завдання реалізують, використовуючи функції ПОИСКПОЗ, ИНДЕКС, ПРОСМОТР, ВПР

Функція ПОИСКПОЗ. Ця функція виконує пошук зазначеного значення у заданому діапазоні комірок і повертає його позицію. Аргументами її є:

Рис. 2.41.. Таблиця значень

1) Искомое_значение — значення, за яким відбу­вається пошук (може бути числом, текстом, логічним значенням або посиланням на комірку із значенням);

2) Просматриваемьій_массив — діапазон комірок, в якому відбувається пошук;

3) Тип_сопоставления — число 0, -1 або 1 (за замов­чуванням — 1). Це означає, що функція знаходить най­більше значення, яке менше або дорівнює значенню, за яким відбувається пошук.

Наприклад, необхідно, користуючись представле­ними на рис. 2.40 та 2.41. значеннями, знайти для кож­ного замовлення (коду) номер фірми-замовника. Для виконання завдання курсор встановлюють у комірку F2 та активізують функцію ПОИСКПОЗ. Задають її аргументи: значення для пошуку — А2, діапазон пошу­ку — $А$3:$А$6 на листку «Довідник» (тут абсолютна адресація використовується для виключення автома­тичної зміни адрес при подальшому копіюванні). Функ­ція матиме вигляд (рис2.40.):

=ПОИСКПС)3(А2;Довідник!$А$3:$А$6).

Функція ИНДЕКС. Вона повертає значення або посилання на нього. Наприклад, необхідно для кожно­го замовлення (коду) вивести назву фірми-замовника. Аргументами функції є:

1. Массив — діапазон комірок, у якому вміщено назви фірм — $В$3:$В$5;

2. Номер_строки — номер рядка із визначеного для пошуку в аргументі Массив діапазону. Функція ПОИСКПОЗ повернула позицію для замовлення А2;

3. Номер_столбца — номер стовпця в діапазоні зна­чень із аргументуМассив. Якщо заданий аргумент Номер_строки, цей аргумент можна не визначати.

Отже, відповідно до поставленого завдання, функція має вигляд:

=ИНДЕКС(Довідник!$В$3:$В$6;ІЮИСКПОЗ (А2;Довідник!$А$3:$А$6)) — рис. 2.37.

Функція ПРОСМОТР. Ця функція виконує пошук у заданому діапазоні комірок потрібного значення і повертає його позицію. Аргументами її є:

1. Искомое_значение — значення, за яким відбу­вається пошук (може бути числом, текстом, логічним значенням або посиланням на комірку із значенням);

2. Просматриваемьій_вектор — діапазон комірок (один рядок або стовпець), в якому відбувається пошук;

3. Вектор_результатов — діапазон комірок (один рядок або стовпець), у якому виводяться результати пошуку.

Наприклад, необхідно знайти для кожного замо­влення (коду) код його оплати. Тоді функція має вигляд (рис. 2.41.):

=ПРОСМОТР(А2; Довідник! $ А$ 3: $ А$ 6; Довідник!$С$3:$С$6).

Функція ВПР. Вона виконує пошук вказаного зна­чення у крайньому лівому стовпці таблиці і повертає значення у тому самому рядку іншого стовпця. Аргу­ментами її є:

1. Искомое_значение — значення, за яким відбу­вається пошук (може бути числом, текстом, логічним значенням або посиланням на комірку із значенням);

2. Таблица — діапазон комірок, у якому відбуваєть­ся пошук;

3. Номер_столбца — номер стовпця з аргументу Таблица, з якого повертається значення.

Наприклад, необхідно, користуючись представлени­ми на рис. 2.40 та 2.41. значеннями, знайти для кожно­го замовлення (коду) тип оплати. Для виконання такого завдання активізують функцію ВПР. Задають її аргу­менти: значення для пошуку — А2, діапазон пошуку — $А$3:$С$6 на листку «Довідник» і значення з третього

стовпця вказаного діапазону повертається. Функція матиме вигляд (рис. 2.41):

=ВПР(А2;Довідник!$А$3:$С$5;3).

Самостійна робота

Опрацювати та проаналізувати основні функції категорії Математичні функції

ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...

Что способствует осуществлению желаний? Стопроцентная, непоколебимая уверенность в своем...

Система охраняемых территорий в США Изучение особо охраняемых природных территорий(ООПТ) США представляет особый интерес по многим причинам...

ЧТО И КАК ПИСАЛИ О МОДЕ В ЖУРНАЛАХ НАЧАЛА XX ВЕКА Первый номер журнала «Аполлон» за 1909 г. начинался, по сути, с программного заявления редакции журнала...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: