Здавалка
Главная | Обратная связь

Функції MS EXCEL для створення табличних форм

ЗМІСТ

1 ТЕОРЕТИЧНІ ВІДОМОСТІ..................................................................................... 4

1.1 Принцип автоматизації проектних розрахунків за допомогою табличного процесора MS EXCEL...................................................................................... 4

1.2 Функції MS EXCEL для створення табличних форм...................................... 5

1.2.1 Математичні й тригонометричні функції - Math & Trig - SUM (СУММ) 5

1.2.2 Посилання й масиви - Lookup & Reference - INDEX (ИНДЕКС)............. 6

1.2.3 Посилання й масиви - Lookup & Reference - VLOOKUP (ВПР)............ 10

1.2.4 Посилання й масиви - Lookup & Reference - HLOOKUP (ГПР)............. 12

1.2.5 Посилання й масиви - Lookup & Reference - MATCH (ПОИСКПОЗ)... 14

1.2.6 Посилання та масиви – Lookup & Reference – ROW (СТРОКА)........... 16

1.2.7 Посилання й масиви - Lookup & Reference - COLUMN (СТОЛБЕЦ).... 17

1.2.8 Посилання й масиви - Lookup & Reference - OFFSET (СМЕЩ)............ 18

1.2.9 Статистичні функції – Statistical – MIN (МИН)...................................... 20

1.2.10 Статистичні функції – Statistical –MAX (МАКС)................................. 21

1.2.11 Статистичні функції - Statistical - FORECAST (ПРЕДСКАЗ).............. 22

2 ЗАВДАННЯ ДО КОНТРОЛЬНОЇ РОБОТИ......................................................... 26

2.1 Вимоги до оформлення контрольної роботи................................................ 26

2.2 Варіанти контрольної роботи........................................................................ 26

2.3 Завдання до контрольної роботи................................................................... 28

Рекомендована література....................................................................................... 137

 


ТЕОРЕТИЧНІ ВІДОМОСТІ

Принцип автоматизації проектних розрахунків за допомогою табличного процесора MS EXCEL

Табличний процесор MS EXCEL дає можливість створювати табличні форми (листа) для проектування (див. рис. Рисунок 1). Частина комірок цієї форми відводиться під вихідні дані, а в усіх інших розташовують формули, які використовують зведені в табличну форму вихідні дані та виконують необхідні розрахунки. Після введення всіх необхідних формул табличний процесор автоматично виконує розрахунки, результати яких заповнюють комірки з відповідними формулами. Таким чином всі розрахунки виконуються в табличних формах для результатів і залишається тільки роздрукувати одержану таблицю з вихідними даними та результатами розрахунків. За таким принципом можна виконати всі технічні та техніко-економічні проектні розрахунки.

 
 

Якщо в комірках для вихідних даних створеної електронної таблиці змінити дані, то в комірках з формулами автоматично з'являться нові результати. Таким чином з допомогою табличного процесора MS EXCEL можна виконувати прості імітаційні розрахунки.

Рисунок 1 – Таблична форма (лист) для проектування

Функції MS EXCEL для створення табличних форм

1.2.1 Математичні й тригонометричні функції - Math & Trig - SUM (СУММ)

Функція СУММ підсумовує всі числа в інтервалі комірок (див. рис. Рисунок 2).

Синтаксис функції СУММ

СУММ(число1;число2; ...),

де

число1, число2... — від 1 до 30 аргументів, для яких потрібно визначити суму.


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

Рисунок 2 – Приклад використання функції СУММ

 

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

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

 

1.2.2 Посилання й масиви - Lookup & Reference - INDEX (ИНДЕКС)

Повертає значення або посилання на елемент таблиці або інтервалу, визначеного номером рядка та номером стовпця (див. рис. Рисунок 3, Рисунок 4).

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

Синтаксис форми масиву:

ИНДЕКС (масив;номер_рядка;номер_стовпця),

де

масив — це діапазон комірок або масив констант;

· якщо масив містить лише один рядок або стовпець, то відповідний аргумент номер_рядка або номер_стовпця не обов'язковий;

· якщо масив має більше одного рядка та більше одного стовпця, а використовується лише один аргумент номер_рядка або номер_стовпця, то функція ИНДЕКС повертає масив із цілого рядка або стовпця аргументу «масив»;

номер_рядка — це номер рядка в масиві, з якого потрібно повернути значення. Якщо аргумент номер_рядка пропущено, то аргумент номер_стовпця обов'язковий;

номер_стовпця — це номер стовпця в масиві, з якого потрібно повернути значення. Якщо аргумент номер_стовпця пропущено, то аргумент номер_рядка обов'язковий;

· якщо використовуються обидва аргументи номер_рядка та номер_стовпця, функція ИНДЕКС повертає значення комірки на перетині вказаного рядка та стовпця;

· якщо установити номер_рядка або номер_стовпця рівним 0 (нулю), то функція ИНДЕКС поверне масив значень для цілого стовпця або рядка, відповідно. Для використання масиву значень введіть функцію ИНДЕКС як формулу масиву в горизонтальний діапазон клітинок для рядка та у вертикальний — для стовпця. Щоб ввести формулу масиву, необхідно натиснути клавіші CTRL+SHIFT+ВВІД;

· номер_рядка та номер_стовпця мають указувати на клітинку в масиві; в іншому випадку функція ИНДЕКС повертає значення помилки #ССЫЛ!.

Таку формулу необхідно вводити як формулу масиву. Для цього необхідно виділити діапазон A2:A3, починаючи із клітинки, яка містить формулу. Потім натиснути клавішу F2, а після цього — клавіші CTRL+SHIFT+ВВІД. Якщо формула не буде введена як формула масиву, єдине значення дорівнюватиме 2.

Рисунок 3 – Приклад використання функції ИНДЕКС

 

Синтаксис форми посилання:

ИНДЕКС (посилання;номер_рядка;номер_стовпця;номер_області),

де

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

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

· якщо кожна область в посиланні містить тільки один рядок або один стовпчик, то аргумент номер_рядка або номер_стовпця є не обов’язковий. Наприклад, для одиничного рядка використовується форма ИНДЕКС (посилання;;номер_стовпця);

номер_рядка — це номер рядка в аргументі посилання на яку повертає посилання;

номер_стовпця — це номер стовпця в аргументі посилання на яку повертає посилання;

номер_області – інтервал посилання, з якого повертається перетин номер_рядка і номер_стовпця. Перша введена або виділена область має номер 1, друга – 2 і т.д. якщо номер_області пропущений, то функція ИНДЕКС використовує область номер 1.

· якщо аргумент "посилання" визначає комірки (A1: B4; D1: E4; G1: H4), номер області 1 відповідає діапазону A1: B4, номер області 2 - діапазону D1: E4, а номер області 3 - діапазону G1: H4 .

Після того як за допомогою аргументів "посилання" і "номер_області" обраний діапазон, за допомогою аргументів "номер_рядка" і "номер_стовпчика"вибирається конкретна комірка: номер рядка 1 відповідає першому рядку діапазону, номер стовпця 1 – його першому стовпцю і т. д. Посилання, яка повертає функція ИНДЕКС, вказує на перетин рядка "номер_рядка" і колонки "номер_стовпчика".

Якщо вказати як аргумент "номер_рядка" або "номер_стовпчика" значення 0, функція ИНДЕКС поверне посилання на цілий стовпець або цілий рядок відповідно.

"Номер_рядка", "номер_стовпчика" і "номер_області" повинні вказувати на комірку всередині аргументу "посилання". В іншому випадку функція ИНДЕКС повертає значення помилки #ССЫЛ!. Якщо аргументи "номер_рядка" і "номер_стовпчика" опущені, функція ИНДЕКС повертає область в аргументі "посилання", задану аргументом "номер_області".

Результатом обчислення функції ИНДЕКС є посилання, яке інтерпретується посиланням іншими функціями. В залежності від формули значення, що повертається функцією ИНДЕКС, може використовуватися як посилання або як значення. Наприклад, формула ЯЧЕЙКА("ширина";ИНДЕКС(A1:B2;1;2)) еквівалентна формулі ЯЧЕЙКА("ширина";B1). Функція ЯЧЕЙКА використовує значення, що повертає функція ИНДЕКС, як заслання. З іншого боку, формула 2*ИНДЕКС(A1:B2;1;2), перетворює значення, що повертається функцією ИНДЕКС, в число в комірці B1.

 
 

Рисунок 4 – Приклад використання функції ИНДЕКС

 

1.2.3 Посилання й масиви - Lookup & Reference - VLOOKUP (ВПР)

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

Синтаксис функції ВПР:

Рисунок 5 – Приклад використання функції ВПР

 
 

ВПР (шукане_значення; таблиця; номер_стовпця; інтервальний_перегляд),

де

шукане_значення – це значення, яке потрібно знайти в першому рядку таблиці. Шукане значення може бути значенням, посиланням або текстовим рядком. Якщо шукане значення менше найменшого значення в першому стовпці масиву «таблиця», ВПР повертає значення помилки #Н/Д;

таблиця – це таблиця з інформацією, у якій шукаються дані. Можна використовувати посилання на інтервал або ім'я інтервалу. Значення в першому стовпці масиву «таблиця» є значеннями, пошук яких виконується за допомогою аргументу «шукане_значення». Ці значення можуть бути текстовими рядками, числами або логічними значеннями. Текстові рядки порівнюються без обліку регістра букв;

номер_стовпчика вказує номер стовпця в масиві «таблиця», в якому необхідно знайти відповідне значення. Якщо «номер_стовпчика» дорівнює 1, то повертається значення з першого стовпця аргументу «таблиця», якщо «номер_стовпчика» дорівнює 2, то повертається значення із другого стовпця аргументу «таблиця» і т. д. Якщо «номер_стовпчика»:

· менше 1, то функція ВПР повертає значення помилки #ЗНАЧ!.

· більше, ніж кількість стовпців масиву «таблиця», то функція ВПР повертає значення помилки #ССЫЛ!;

інтервальний_перегляд – це логічне значення, яке визначає, чи потрібно, щоб функція ВПР шукала точну або наближену відповідність:

 

· якщо цей аргумент має значення ИСТИНА (або пропущений), то функція ВПР повертає точне або приблизно відповідне значення. Якщо точна відповідність не знайдена, то функція ВПР повертає наступне максимальне значення, яке менше, ніж шукане_значення. Значення в першому стовпці аргументу «таблиця» повинні бути розташовані в порядку зростання: ..., –2, –1, 0, 1, 2, ..., A—Z, ЛОЖЬ, ИСТИНА; в іншому випадку функція ВПР може видати неправильний результат. Дані можна впорядкувати такий спосіб: у меню Данные вибрати команду Сортировка і встановити перемикач По возрастанию.

· якщо цей аргумент має значення ЛОЖЬ, то функція ВПР поверне тільки точну відповідність. В цьому випадку значення в першому стовпці масиву «таблиця» не обов'язково повинні бути відсортовані. Якщо в першому стовпці масиву «таблиця» аргументу «шукане_значення» відповідає два і більше значень, використовується перше знайдене значення. Якщо знайти точну відповідність не вдається, то повертається значення помилки #Н/Д..

У випадку пошуку текстових значень необхідно, щоб дані в першому стовпці масиву «таблиця» були без пробілів на початку і в кінці рядка, прямих (' або ") і вигнутих (‘ або “) лапок або недрукованих знаків. В протилежному випадку функція ВПР може повернути неправильне або неочікуване значення.

У випадку пошуку числових значень або дат необхідно, щоб дані в першому стовпці масиву «таблиця» зберігались не як текстові значення. В цьому випадку ВПР може повернути неправильне або неочікуване значення.

Якщо масив «інтервальний_перегляд» має значення ЛОЖЬ, а значення масиву «інтервальний_перегляд» мають текстовий формат, в масиві «інтервальний_перегляд» можна використовувати символи узагальнення, знак питання (?) і зірочку (*). Знак питання відповідає будь-якому знаку; зірочка відповідає будь-якій послідовності знаків. Якщо потрібно знайти знак питання або зірочку, то перед ними слід поставити знак тильда (~).

 

1.2.4 Посилання й масиви - Lookup & Reference - HLOOKUP (ГПР)

Функція ГПР шукає значення у верхньому рядку таблиці або масиву (див. рис. Рисунок 6). Ця функція використовується, коли значення, що порівнюються розташовані у верхньому рядку таблиці даних, а повертані — на кілька рядків нижче. Якщо порівнювані значення знаходяться в стовпці зліва від шуканих даних, слід використовувати функцію ВПР.

Синтаксис функція ГПР:

Рисунок 6 – Приклад використання функції ГПР

ГПР(шукане_значення;таблиця;номер_рядка;інтервальний_перегляд),

де

шукане_значення— значення, яке потрібно знайти в першому рядку таблиці. «Шукане_значення» може бути значенням, посиланням або текстовим рядком;

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

· значення в першому рядку аргументу «таблиця» можуть бути текстом, числами або логічними значеннями.

· якщо аргумент «інтервальний_перегляд» має значення ИСТИНА, то значення в першому рядку аргументу «таблиця» повинні бути розташовані в порядку зростання: ...–2, –1, 0, 1, 2 ..., A—Z, ЛОЖЬ, ИСТИНА. В протилежному випадку функція ГПР може видати неправильний результат. Якщо ж аргумент «інтервальний_перегляд» має значення ЛОЖЬ, сортування для аргументу «таблиця» не обов'язкова.

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

· дані можна впорядкувати за зростанням, зліва направо. Для цього необхідно виділити ці дані і вибрати у меню Данные команду Сортировка. Потім натиснути кнопку Параметры та вибрати столбцы диапазона. Після цього натиснути кнопку ОК. У полі Сортировать по вибрати рядок в списку і встановити перемикач по возрастанию;

номер_рядка — номер рядка в масиві «таблиця», з якої буде повернене значення, що порівнюється. Якщо значення аргументу «номер_рядка» дорівнює 1, то повертається значення з першого рядка аргументу «таблиця», якщо воно дорівнює 2 — з другого рядка і так далі Якщо значення аргументу «номер_рядка» менше 1, функція ГПР повертає значення помилки #ЗНАЧ!; якщо воно більше, ніж кількість рядків в аргументі «таблиця», повертається значення помилки # ССЫЛ!;

інтервальний_перегляд — логічне значення, яке визначає, яку відповідність повинна шукати функція ГПР — точну або приблизну. Якщо цей аргумент має значення ИСТИНА або опущений, то повертається приблизно відповідне значення; за відсутності точної відповідності повертається найбільше із значень, менших, ніж «шукане_значення». Якщо цей аргумент має значення ЛОЖЬ, функція ГПР шукає точну відповідність. Якщо воно не знайдене, повертається значення помилки #Н/Д.

 

1.2.5 Посилання й масиви - Lookup & Reference - MATCH (ПОИСКПОЗ)

Функція ПОИСКПОЗ повертає відносне положення елемента масиву, який відповідає вказаному значенню в заданому порядку (див. рис. Рисунок 7). Цією функцією варто використовувати, коли потрібно знайти позицію елемента в діапазоні, а не сам елемент.

Рисунок 7 – Приклад використання функції ПОИСКПОЗ

 
 

Синтаксис функції ПОИСКПОЗ

ПОИСКПОЗ(шукане_значення;масив_що_переглядається;тип_зіставлення),

де

шукане_значення — значення, яке використовується для пошуку значення в таблиці:

· шукане_значення — це значення, що зіставляється зі значеннями в аргументі що масив_що_переглядається. Наприклад, у випадку пошуку тривало-допустимого струму кабелю переріз кабелю буде шуканим значенням, а потрібним значенням буде тривало-допустимий струм;

· шукане_значення може бути значенням (числом, текстом або логічним значенням) або посиланням на комірку, що містить таке значення;

масив_що_переглядається — неперервний інтервал комірок, що можуть містити шукані значення. Масив_що_переглядається може бути масивом або посиланням на масив;

тип_зіставлення — число –1, 0 або 1. Аргумент тип_зіставлення вказує, яким чином Microsoft Excel зіставляє шукане_значення зі значеннями в аргументі масив_що_переглядається.

· якщо тип_зіставлення дорівнює 1, то функція ПОИСКПОЗ знаходить найбільше значення, що менше або дорівнює значенню аргументу шукане_значення. Масив_що_переглядається повинен бути впорядкований за зростанням: ..., –2, –1, 0, 1, 2, ..., A—Z, ЛОЖЬ, ИСТИНА;

· ЯКЩО тип_зіставлення дорівнює 0, то функція ПОИСКПОЗ знаходить перше значення, яке дорівнює аргументу шукане_значення. Масив_що_переглядається може бути не впорядкований.

· якщо тип_зіставлення дорівнює –1, то функція ПОИСКПОЗ знаходить найменше значення, що більше або дорівнює значенню аргументу шукане_значення. Масив_що_переглядається повинен бути впорядкований за зменшенням: ИСТИНА, ЛОЖЬ, Z—A, ..., 2, 1, 0, –1, –2, ... і т. д.

· якщо аргумент тип_зіставлення пропущений, то вважається, що він дорівнює 1.

Функція ПОИСКПОЗ повертає не саме значення, а його позицію в аргументі масив_що_переглядається, а не саме значення. Наприклад, ПОИСКПОЗ("б";{"а";"б";"в"};0) повертає 2 — відносну позицію букви «б» у масиві {"а";"б";"в"}.

Функція ПОИСКПОЗ не розрізняє регістри при порівнянні текстів.

Якщо функція ПОИСКПОЗ не знаходить відповідне значення, то повертається значення помилки #Н/Д.

Якщо тип_зіставлення дорівнює 0 і шукане_значення є текстом, то шукане_значення може містити знаки шаблону: зірочку (*) і знак питання (?). Зірочка відповідає будь-якій послідовності знаків, знак питання — будь-якому одиночному знаку. Якщо потрібно знайти сам знак питання або зірочку, перед ними слід ввести знак тильди (~).

 

1.2.6 Посилання та масиви – Lookup & Reference – ROW (СТРОКА)

Функція СТРОКА повертає номер рядка, на який вказує посилання (див. рис. Рисунок 8).

Синтаксис функції СТРОКА

 
 

СТРОКА(посилання),

Рисунок 8 – Приклад використання функції СТРОКА

де

 

посилання — це комірка або діапазон комірок, для яких потрібно визначити номер рядка.

 

Якщо посилання пропущене, то вважається, що це посилання на комірку, в якій міститься сама функція СТРОКА.

Якщо посиланням є діапазоном клітинок, а функція СТРОКА введена як вертикальний масив, то функція СТРОКА повертає номери рядків у посиланні як вертикальний масив.

Посилання не може вказувати на декілька областей.

 

1.2.7 Посилання й масиви - Lookup & Reference - COLUMN (СТОЛБЕЦ)

Функція СТОЛБЕЦ повертає номер стовпчика, на який вказує посилання (див. рис. Рисунок 9).

Рисунок 9 – Приклад використання функції СТОЛБЕЦ

 
 

Синтаксис функції СТОЛБЕЦ

СТОЛБЕЦ (посилання),

де

посилання — це комірка або діапазон комірок, для яких потрібно визначити номер стовпчика.

Якщо посилання пропущене, то вважається, що це посилання на комірку, в якій міститься сама функція СТОЛБЕЦ.

Якщо посиланням є діапазоном клітинок, а функція СТОЛБЕЦ введена як горизонтальний масив, то функція СТОЛБЕЦ повертає номери рядків у посиланні як горизонтальний масив.

Посилання не може вказувати на декілька областей.

 

1.2.8 Посилання й масиви - Lookup & Reference - OFFSET (СМЕЩ)

Функція СМЕЩ повертає посилання на діапазон, віддалений від клітинки або діапазону клітинок на вказану кількість рядків і стовпців (див. рис. Рисунок 10). Вказане посилання може бути окремою клітинкою або діапазоном клітинок. Можна задавати кількість рядків і стовпців, які повертаються.

Синтаксис функції СМЕЩ

Рисунок 10 – Приклад використання функції СМЕЩ

СМЕЩ(посилання;зміщ_по_рядках;зміщ_по_стовпчиках;висота;ширина),

де

посилання— посилання, від якої обчислюється змішення. Аргумент "посилання" повинне бути посиланням на комірку або на діапазон суміжних комірок, в іншому випадку функція СМЕЩ повертає значення помилки #ЗНАЧ!;

зміщ_по_рядках — кількість рядків, які потрібно відрахувати вгору або вниз, так щоб верхня ліва комірка результату посилалася на це місце. Якщо задане, наприклад, число 5 як значення аргументу зміщ_по_рядках, то це вказує, що ліва верхня комірка результату посилання повинна бути на п'ять рядків нижче аргументу посилання. Зміщ_по_рядках може бути додатнім (нижче початкового посилання) або від'ємним (вище початкового посилання);

зміщ_по_стовпчиках — кількість стовпчиків, які потрібно відрахувати вліво або вправо, так щоб ліва верхня комірка результату посилалася на це місце. Якщо задане, наприклад, число 5 як значення аргументу зміщ_по_стовпчиках, то це вказує, що ліва верхня клітинка результату посилання повинна бути на п'ять стовпчиків правіше аргументу посилання. Зміщ_по_стовпчиках може бути додатнім (вправо від початкового посилання) або від'ємним (ліворуч від початкового посилання);

висота — висота (кількість рядків) результату посилання. Висота повинна бути додатнім числом;

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

Якщо зміщ_по_рядках і зміщ_по_стовпчиках виводять посилання за межі робочого аркуша, то функція СМЕЩ повертає значення помилки #ССЫЛ!.

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

Функція СМЕЩ фактично не переміщає комірки і не змінює виділення. Вона тільки повертає посилання. Функція СМЕЩ можна використовувати з будь-якою функцією, в якій очікується аргумент типу посилання. Наприклад, формула СУММ(СМЕЩ(C2;1;2;3;1)) обчислює суму діапазону, що складається з трьох рядків і одного стовпця і розташованого одним рядком нижче і двома стовпцями правіше комірки C2.

 

 





©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.