47348

Обчислення в середовищі MS Excel з використанням функцій

Лекция

Информатика, кибернетика и программирование

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

Украинкский

2014-03-30

887.98 KB

3 чел.

ЛЕКЦІЯ 4

Тема: Обчислення в середовищі MS Excel з використанням функцій

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

План

  1.  Застосування імен у формулах Excel.
  2.  Поняття функції MS Excel. Автосума. Майстер функцій.
  3.  Застосування імен у формулах Excel

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

В якості імені в MS Excel може бути використана довільна послідовність букв, цифр і символів підкреслення «_», що починається з букви або символу підкреслення, довжина якої не перевищує 255 символів. При цьому рекомендується використовувати імена довжиною не більше 15 символів.

При створенні або редагуванні імен необхідно дотримуватися таких правил:

  1.  як і в більшості мов програмування перший символ імені повинен бути буквою, символом підкреслення «_» або зворотною нахиленою рискою «\». При цьому не можна використовувати великі й малі букви «C», «c», «R» або «r» як визначене ім’я;
  2.  імена не можуть бути ідентичні посиланням на клітинки, на зразок «Z$100» або «R1C1»;
  3.  в іменах не можна використовувати пробіли, для відокремлення слів потрібно використовувати символ підкреслення «_» і крапку «.»;
  4.  імена можуть містити букви як верхнього, так і нижнього регістрів, але MS Excel не розрізняє регістр символів в іменах.

Існує декілька типів імен, які можна створити та використовувати.

  1.  Визначене ім’я – ім’я, яке позначає комірку, діапазон комірок, формулу або константу. Можна самостійно створити визначене ім’я, а іноді програма MS Excel створює визначене ім’я сама (наприклад, під час визначення області друку).
  2.  Ім’я таблиці – це ім’я для таблиці Excel, яка визначається як сукупність даних про певний предмет, збережених у записах (рядках) і полях (стовпцях). Excel створює стандартне ім’я таблиці Excel (типу Таблиця1, Таблиця2 тощо) під час кожного додавання таблиці Excel, але ім’я таблиці можна змінити, щоб зробити його більш зрозумілим для себе.

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

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

Поле імен – це поле зі списком, використовуючи який можна виділити відповідний діапазон. Крім того, що поле імен може бути використане для визначення імені, його можна також використовувати для вставлення вибраного імені у формулу.

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

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

  1.  виділити діапазон, якому потрібно надати ім’я, разом із підписами рядків або стовпців;
  2.  виконати команду Створити з виділеного групи Визначені імена вкладки Формули. Після цього відкривається діалогове вікно Створення імен з виділення, рис. 4.1.:

Рис. 4.1. Діалогове вікно Створення імен з виділення

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

Інший спосіб, третій, створення імен ґрунтується на використанні діалогового вікна Нове ім’я (рис. 4.2.). Для того, щоб відкрити це вікно необхідно виконати команду Визначити ім’я… колекції Визначити ім’я групи Визначені імена вкладки Формули.

У діалоговому вікні Нове ім’я у полі Ім’я потрібно ввести певну комбінацію символів, яка буде використовуватися як посилання.

У полі Область визначається область дії імені, що створюється, тобто з пропонованого списку необхідно вибрати пункт Робоча книга або назву потрібного аркуша. За потреби можна ввести у полі Примітка описову примітку довжиною до 255 символів.

Рис. 4.2. Діалогове вікно Нове ім’я

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

Для того, щоб завершити операцію присвоєння певному об’єкту імені потрібно закрити діалогове вікно Нове ім’я натиснувши кнопку ОК.

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

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

Для того, щоб скористатися локальним іменем на іншому аркуші, можна уточнити це ім’я, вказавши перед ним ім’я аркуша, наприклад:

Аркуш4!новийДіапазон.

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

Ім’я завжди повинно бути унікальним у своїй області. Проте можна використовувати одне й те ж ім’я в різних областях. Наприклад, можна визначити ім’я на зразок «новийДіапазон» з областю дії на аркушах Аркуш1, Аркуш2 та Аркуш3 в одній і тій самій книзі.

За замовчуванням у програмі MS Excel ім’я, визначене для аркуша, має вищий пріоритет над глобальним рівнем робочої книги.

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

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

Рис. 4.3. Діалогове вікно Диспетчер імен

У діалоговому вікні створені імена відображаються спеціальними піктограмами. Наприклад:

  1.  ім’я комірки, діапазону позначено піктограмою визначеного імені.
  2.  ім’я таблиці позначено піктограмою імені таблиці.

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

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

Таблиця 4.1.

Команди фільтрування імен

Команда

Результат

Імена на аркуші

відображаються імена локального рівня області дії (рівня аркуша)

Імена у книзі

відображаються імена глобального рівня області дії (рівня книги)

Імена з помилками

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

Імена без помилок

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

Визначені імена

відображаються імена, комірок, діапазонів які визначені користувачем або автоматично MS Excel, наприклад Область друку

Імена таблиць

відображаються тільки імена таблиць

Використовуючи кнопки Створити…, Редагувати… та Видалити діалогового вікна Диспетчер імен можна відповідно створити нове ім’я (відкривається вікно Нове ім’я), зредагувати виділене ім’я (відкривається вікно Редагувати ім’я) та видалити виділене ім’я.

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

Для того, щоб скористатися ім’ям у формулі можна:

  1.  ввести ім’я з клавіатури

або

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

Рис. 4.4. Діалогове вікно Вставлення імені

  1.  Поняття функції MS Excel. Автосума. Майстер функцій

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

Список аргументів може складатися з чисел, тексту, логічних величин, масивів або посилань. При вказуванні аргументів необхідно слідкувати за відповідністю типів. Крім того, аргументи можуть бути як константами, так і виразами – формулами. Ці формули у свою чергу можуть містити інші функції. Формули, що використовуються як аргументи функції, повинні створювати припустимі для цього аргументу значення. Деякі функції можуть мати порожній список аргументів.

Кожна функція має ім’я.

Введення функції починається з вказування її імені, потім вводиться символ "(" дужка, яка відкривається, вказуються аргументи, які відокремлюються символом ";" (крапка з комою), в кінці – символ ")" дужка, яка закривається.

Якщо написання формули починається з функції, перед іменем функції вводиться символ "=" (рівності).

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

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

Рис. 4.5. Контекстна підказка (автозаповнення) імен (функцій)

Після введення імені функції та відкритої дужки, наприклад HEX2BIN( відображається висхідна підказка із синтаксисом функції та її аргументами, (рис. 4.6.). Підказки відображаються тільки для вбудованих функцій. Аргументи у функції можуть бути обов’язковими і необов’язковими. У довідковій інформації MS Excel необов’язкові аргументи виділяються менш жирним шрифтом.

Рис. 4.6. Контекстна підказка синтаксису функції

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

Рис. 4.7. Список функцій поля рядка формул

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

Якщо при виклику функції деякий аргумент випущений, то символ крапка з комою «;» після опущеного аргументу все одно повинен бути поставлений (за умови що цей аргумент – не останній). Наприклад, BETAINV(1;2;3;;4).

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

Функції, які дозволяють знайти суму даних (SUM()), визначити середнє (AVERAGE()), максимальне (MAX()), мінімальне значення (MIN()), кількість чисел в діапазоні (COUNT()), винесені на вкладку Основне група Редагування кнопка зі списком Автосума. Кнопка Автосума дублюється на вкладці Формули група Бібліотека функцій.

Для того, щоб використати засіб Автосума для обчислень можна використати один із декількох варіантів дій:

  1.  виділити комірки діапазону, над якими виконуються обчислення, розкрити список кнопки Автосума , вибрати необхідну дію, у наступній комірці, нижче або правіше від виділеного діапазону, буде відображений результат застосування відповідної функції, аргументом якої є виділений діапазон;
  2.  виділити комірки діапазону, над якими виконуються обчислення, а також комірку, в яку розміщується результат (нижче або правіше діапазону), розкрити список кнопки Автосума , вибрати необхідну дію, у виділеній додатковій комірці буде відображений результат застосування відповідної функції, аргументом якої є виділений діапазон;
  3.  зробити активною комірку, в яку буде розміщено результат обчислень, розкрити список кнопки Автосума , вибрати необхідну дію, MS Excel автоматично запропонує діапазон, який буде виступати аргументом вибраної функції, за необхідності змінити запропонований діапазон, натиснути клавішу Enter, після чого у вибраній комірці відобразиться результат обчислень.

Серед елементів списку Автосуми є команда Інші функції, за допомогою якої відкривається вікно Вставлення функції (рис. 4.8.). Для того, щоб відкрити це вікно для вставлення функції, крім раніше зазначених способів, можна використати також кнопку Вставити функцію групи Бібліотека функцій вкладки Формули або команду Інші функції довільної колекції групи Бібліотека функцій або натиснути комбінацію клавіш Shift+F3.

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

Рис. 4.8. Діалогове вікно Вставлення функції

Всі вбудовані функції MS Excel згруповані за категоріями, які можна переглянути та вибрати у полі зі списком Категорія. Крім категорій список цього поля містить декілька додаткових елементів, які виділені для спрощення пошуку функцій, а саме: Нещодавно використані – 10 функцій, що використовувалися останніми та Усі – містить список усіх функцій, впорядкованих за алфавітом.

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

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

Рис. 4.9. Діалогове вікно Вставлення функції

Аргументи функції можуть бути обов’язковими і необов’язковими. У діалоговому вікні Аргументи функції назва обов’язкового аргументу виділена більш жирним шрифтом.

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

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

У режимі введення формули з використанням функції кнопка рядка формул дозволяє згорнути/розгорнути вікно Аргументи функції.

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

У певних випадках можна використати функцію як один із аргументів.

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

Формула може містити до семи рівнів вкладених функцій. Якщо функція Б використовується як аргумент у функції A, то функція Б – це функція другого рівня.


 

А также другие работы, которые могут Вас заинтересовать

46719. Международная миграция. Виды и роль международной трудовой миграции. Миграционная политика 28.71 KB
  Миграционная политика Миграция населения это добровольное перемещение переселение жителей в пределах страны из одного региона в другой регион внутренняя миграция или же переселение из одного государства в другую страну международная миграция. Международная миграция рабочей силы представляет собой процесс перемещения трудовых ресурсов из одной страны в другую с целью трудоустройства на более выгодных условиях чем в стране происхождения. Под эмиграцией понимается выезд из страны на постоянное место жительства под иммиграцией ...
46723. История развития рекламы в России 27.54 KB
  Формы рекламы такие как этикетка. Золотой век фото рекламы в Р. относят новый вид фоторекламы это брошюры буклеты каталоги с использованием фотоснимков.
46724. Организационные структуры предприятий 27.73 KB
  Большую часть организационных структур механического бюрократического типа можно разделить на две категории: функциональные и дивизиональные структуры см. Этот вид структуры результат департаментации разделения управления на элементы отделы каждый из которых имеет свою определенную задачу в управлении то есть выполняет определенную функцию. Выделяют 4 принципа деления структуры предприятия: 1 функциональный исходя из функций образуют финансовое подразделение производственные подразделения отдел маркетинга и т.
46726. Литература 18 века 28 KB
  С последовательностью и разносторонностью ученого Радищев рассмотрел и подверг уничтожающей критике в Путешествии всю самодержавнокрепостническую общественную систему несущую народу горе. С присуще ему аналитической глубиной Радищев осветил связь таких явлений как падение нравственности народа и разврат верхов нижние заражаются от верхних а от них язва разврата достает и до деревень как взаимная зависимость самодержавия и церкви. Наблюдая вакханалию угнетения крестьянства видя моральную деградацию верхов Радищев находил...