Інформатика і системологія

Тема 5 Табличний процесор Ms Excel

Стислий конспект

Ключові терміни:

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

5.1  Історія розвитку електронних таблиць

Вперше електронні таблиці з'явилися в 1979 році. Це були VisiCalc. Особливість електронних таблиць полягає в тому, що інформація заноситься і обробляється в таблицях. Цю ідею запропонував 26-річний програміст Бріклін. Електронні таблиці виявилися сприятливим ґрунтом для подальшого розвитку.

У 1982 році фірма Lotus Development створила пакет Lotus 1-2-3 для машин типу IBM PC. В цьому пакеті можлива велика швидкість обробки даних.

У 1983 році фірма Microsoft почала роботу над пакетом MultiPlan. Над його створенням працювали Пол Аллен і Білл Гейтс. Кландер паралельно з Windows працював над новим типом електронних таблиць. Спочатку вона називалася Odyssey, потім перетворилася в Excel.

 

5.2 Обробка даних засобами електронних таблиць

Електронна таблиця - це комп'ютерний еквівалент звичайної таблиці, в комірках якої записані дані різних типів: тексти, дати, формули, числа.

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

Застосування електронних таблиць спрощує роботу з даними і дозволяє одержувати результати без проведення розрахунків вручну чи спеціального програмування. Електронні таблиці знайшли широке застосування в економічних і бухгалтерських розрахунках. Їх можна ефективно використовувати для:

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

Табличний процесор Microsoft Excel дає можливість вирішувати багато досить складних фінансово-економічних задач, здатний задовольнити потреби багатьох фахівців з економіки, банківської справи, менеджменту і маркетингу, а також фахівців з інших областей знань.

Надалі ми розглянемо основні поняття і прийоми роботи з передостанньою версією програми - Microsoft Excel 2010, що входить разом з іншими програмами в пакет Microsoft Office 2010.

5.3 Microsoft Excel 2010

Microsoft Excel (також іноді називається Microsoft Office Excel) - програма для роботи з електронними таблицями, створена корпорацією Microsoft для Microsoft Windows, Windows NT і Mac OS. Вона надає можливості економіко-статистичних розрахунків, графічні інструменти і, за винятком Excel 2008 під Mac OS X, мова макропрограмування VBA (Visual Basic for Application). Microsoft Excel входить до складу Microsoft Office і на сьогоднішній день Excel є одним з найбільш популярних додатків в світі.

Що таке Excel 2010

Електронна таблиця (у тому числі і Excel) - це інтерактивна комп'ютерна програма, основне робоче поле якої складається з набору рядків і стовпчиків. Вони відображаються на екрані в спеціальному вікні, яке можна прокручувати вгору і вниз, вправо і вліво.

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

Окрім обробки та аналізу табличних даних, Excel надає можливість:

5.4 Основні елементи електронних таблиць

Робоча книга і робочий лист. Рядки і Стовпці

 

Файл, створений в Excel, називається робочою книгою.

Файл робочої книги Excel має розширення *.xls.

При створенні нового файлу - книги, їй привласнюється за замовчуванням ім'я КНИГА1 і т.і. Перейменування виконується шляхом збереження файлу під новим ім'ям.

Книга в Microsoft Excel є файлом, що використовується для обробки і зберігання даних.

Одним з основних компонентів книги є електронні таблиці.

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

 

Комірки(клітини)

Робочий лист розділений на стовпці i рядки, на перетині яких утворюються комірки(клітини).

Стовпчики таблиці позначаються прописними латинськими буквами і, далі, двохлітерними комбінаціями. Всього робочий лист може містити до 256 стовпців, пронумерованих від А до IV.

Рядки послідовно нумеруються числами, від 1 до 65536 (максимально допустимий номер рядка).

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

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

 

Характеристики комірки

Основним елементом електронної таблиці є комірка.

Характеристики комірки(клітинки)

  1. Адреса.
  2. Вміст.
  3. Значення.
  4. Рівень захисту
  5. Формат відображення.

1. Адреса вказує на місце розташування комірки в таблиці.

2. Вміст комірки можна побачити в рядку введення (рядку формул).

  1. Текст
  2. Число
  3. Формула
  4. Функція

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

  1. Текст.
  2. Число.
  3. Дата.
  4. Помилка.
  5. Рівень захисту. На комірку можна встановити захист від змін. Захист особливо необхідно встановлювати на комірки з формулами або з важливими незмінними даними.
  6. Формат відображення - необхідний для надання документу зручного вигляду. Це, як правило, досягається в результаті форматування.

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

Діапазон комірок

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

Діапазон комірок позначається адресами кутових комірок (лівої верхньої і правої нижньої), розділених двокрапкою. Наприклад, C3:F5 - це блок комірок, розташованих на перетині стовпців С, D, E, F і рядків 3, 4, 5, тобто C3, D3, E3, F3, C4, …, F5.

5.4.1 Основні режими роботи в Excel 2010

Режими роботи в Excel забезпечують:

Відкриття Excel  і вихід з середовища Excel 

Способи активізації середовища Excel:

  1. Подвійне клацання по ярлику Excel на Робочому столі;
  2. Пуск → Все программы → Microsoft Office  → Microsoft Excel      
  3. Панель Швидкого запуску на панелі задач: клацання по ярлику Excel.

Вихід з середовища  Excel:

  1. Клацання по кнопці в заголовку Excel
    1. Клацання по кнопці системного меню Excel
    2. У вікні системного меню  вибрати пункт Закрыть (Close)
  2. Комбінація клавіш ALT/F4

​Структура вікна Excel

Основні елементи інтерфейсу Excel:

  1. Заголовок.
  2. Меню-Вкладки.
  3. Панель інструментів.
  4. Поле імені (адреса поточної комірки)
  5. Рядок формул (вміст комірки).
  6. Робоча область книги Excel.
  7. Рядок стану.

Рисунок 1   —  Вікно середовища Excel

 

Кнопка управління вікном середовища Excel

Кнопка системного меню

Насправді ця кнопка є піктограмою Excel . Якщо клацнути на ній, то відкриється системне меню, що дозволяє маніпулювати вікном Excel (змінювати його розміри, переміщати та закривати).

Кнопка мінімізації (Згорнути)

Клацання по кнопці згортає вікно Excel, і її піктограма поміщається на панель задач Windows.

Кнопка максимізації (Розгорнути)

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

Кнопка закриття

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

При виході з середовища Excel програма запропонує вам зберегти не збережені файли.

Рядок формул(вміст комірки)

Дані або формули, що вводяться в комірку Excel-таблиці відображаються в цьому рядку.

Поле імені(адреса поточної комірки)

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

Рядок стану

В цій області відображаються різні повідомлення, а також стан клавіш <Num Lock> <Caps Lock> та <Scroll Lock>.

Структура вікна робочої книги

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

Вікно робочої книги і вікно програми Excel мають багато спільного.

Рисунок 2   - Вікно середовища Excel 2010 і середовища таблиці Книга 1.

Кнопка виділення всіх комірок робочого листа

Клацання на цій кнопці (яка знаходиться на перетині заголовків рядків і стовпців) виділяє всі комірки активного робочого листа поточної робочої книги.

Табличний курсор - це темний контур, що виділяє поточну активну комірку (одну з 16777216 комірок кожного робочого листа).

Заголовки рядків

Кожний рядок робочого листа має заголовок, який є числом в інтервалі від 1 до 65536. Для того, щоб виділити всі комірки рядка, клацніть на заголовку рядка.

Заголовки стовбців

Кожен з 256 стовпців робочого листа має заголовок - букву від А до Z, далі - від AA до IV. Після стовпця Z йде стовпець АА, за яким слідують АВ, АС і т.д. Після стовпця AZ йдуть ВА, ВВ і так до самого останнього, який позначається IV. Щоб виділити всі комірки стовпця, клацніть на його заголовку.

Кнопки прокрутки ярличків листів

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

Режими представлення таблиці

Microsoft Excel дозволяє працювати з таблицями в двох режимах:

Для переходу між режимами "Обычный" і "Разметка страниц" використовуються відповідні пункти меню "Вид".

Діалогові вікна програми Excel

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

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

Рисунок 3   - Діалогові вікна Excel

​Відкриття книги Excel

  1. Натисніть меню Вкладку "Файл" -  "Открыть".
  2. Зі списку "Папка" виберіть диск, що містить потрібний документ.
  3. В списку "Папка" встановіть покажчик на потрібну папку і двічі натисніть кнопку миші. Відкривайте папки до тих пір, поки не відкриєте папку, що містить потрібний документ.
  4. Із списку файлів виберіть ім'я документу.
  5. Натисніть кнопку "Открыть".

Щоб відкрити документ, що недавно використовувався, виберіть його ім'я зі списку файлів в нижній частині меню Файл - Последние. Якщо цей список відсутній, в меню Файл - Параметры - Дополнительно Раздел "Экран" - "Число документов в списке последних файлов".

Вставка листа

Щоб вставити окремий лист, у нижній частині листа натисніть ярличок - .

Щоб вставити декілька листів, виберіть необхіді листки, утримуючи натиснуту клавішу SHIFT+F11.

Вставка листа діаграми

  1. Виділіть рядки або стовпці, що містять дані і текст для підписів, з яких необхідно побудувати діаграму.
  2. В меню Вставка виберіть пункт "Диаграмма" та натисність у правому нижньому куті, з'явиться діалогове вікно "Вставка диаграммы"
  3. Дотримуйтесь інструкцій майстра діаграм.

Видалення листів з книги

  1. Виділити листи, які слід видалити.
  2. Відкрити контекстне меню, вибрати команду "Удалить".


Зберігання та Перейменування книги

  1. Перейдіть в книгу, яку слід перейменувати.
  2. В меню Файл виберіть команду Сохранить как.
  3. В полі зі списком, що розкривається "Имя файла" наберіть нову назву.

Примітка

a) У файлі з початковим ім'ям залишиться попередня копія книги.

b) Не використовуйте символи кирилиці, щоб уникнути непередбачуваних реакцій програми і системи Windows.

c) Пропоноване за замовчуванням програмою Excel у вікні «Тип файла» розширення файлу .xls, змінювати не бажано.

Після того, як будуть зроблені всі установки, потрібно закрити діалогове вікно клацанням по кнопці OK

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

 

Таблиця 1  . Виділення комірок, діапазонів.

Щоб виділити

Виконати наступне
 

Окрему комірку

Одне клацання по комірці чи перейти до нею, використовуючи клавіші переміщення ­ →.

Діапазон комірок

Протягнути покажчик миші від першої комірки діапазону до останньої або

Виділити першу комірку і утримуючи натиснутою клавішу SHIFT натискати відповідно клавіші →­.

 

Більша або менша кількість комірок в порівнянні з поточним діапазоном

Утримуючи натиснутою клавішу SHIFT, виділити останню комірку, яку необхідно включити в діапазон, що знову виділяється.

Прямокутна область між поточною коміркою і вказаною коміркою утворює новий діапазон.

 

Всі комірки листа

Натиснути кнопку «Выделить все»

.

 

Несуміжні комірки або

діапазони комірок

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

 

Великий діапазон комірок

Виділити першу комірку, потім, утримуючи натиснутою клавішу SHIFT, виділити останню комірку діапазону.

Для переміщення до останньої комірки можна використовувати смуги прокрутки

 

Весь рядок

Виділити заголовок рядка

 

Весь стовпець

Виділити заголовок стовпця.

 

Суміжні рядки або стовпці
  1. Протягнути покажчик по заголовках рядків або стовпців або
  2. Виділити перший рядок або перший стовпець, потім, утримуючи натиснутою клавішу SHIFT, виділити останній рядок або останній стовпець діапазону

 

Несуміжні рядки або стовпці

Виділити перший рядок або перший стовпець,

потім, утримуючи натиснутою клавішу CTRL, виділити решту рядків або стовпців.

 

 

Відображення таблиці в розрахунковому і формульному вигляді

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

Вибрати пункт меню Файл=>Параметры=> Формулы. 

  Для відображення таблиці в розрахунковому вигляді необхідно:

Вибрати пункт меню Файл→Параметры→Дополнительно - зняти прапорець "Показывать формулы, а не их значения".

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

Вибрати в меню ФАЙЛ →Печать→ Параметры страницы => Лист => Встановити прапорець Заголовки строк и столбцов 

Типи даних (текстові, дата, число, функція, формула)

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

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

Таблиця 2  . Типи даних

 

Тип даного

Приклад введення

Вирівнюється

Результат

текст

Це текст

Лівий

Це текст

12345.67

12345.67

12-14-77

12-14-77

'12345,67

12345,67

''12345,67'

'12345,67'

"23456"

"23456"

число

12345,67

Правий

12345,67

дата

4/03/03

Правий

4/03/03

формула

  

Залежить від типу результату виконання формули

="За "&ГОД(СЕГОДНЯ())& "рік"

Текстові

За 2003 рік

=СУММЕСЛИ(A11:A15;"> 160000";B11:B15)

Числа

256,77

=A1>B2

Логічні

ЛОЖЬ

Ехсеl спочатку інтерпретує дані як формула. Введення формули завжди починається з символу = (знака дорівнює).Якщо не формула, то як число або дата. Якщо дане не число і не дата, то дані розглядаються як текст.

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

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

Це виконується викликом настройок «Язык и стандарты»:

Пуск→Панель управления →Язык и стандарты.

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

Рисунок 4   —  «Язык и стандарты»

Введення тексту і чисел

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

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

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

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

Дані, що вводяться, відображаються як в комірці, так і в рядку формул.

Для завершення введення даних використовують клавішу ENTER на клавіатурі або кнопку  Enter в рядку формул .

 

 

Рисунок 5  -  Екран рядка формул Excel.

В рядку введення (формул) видимий повний текст, тобто в пам'яті комп'ютера повний текст.

Редагування даних

Для редагування даних (зміни, додавання і т.д.), введених раніше в комірку, її необхідно заздалегідь виділити (активізувати).

Способи редагування

  1. Активізувати рядок формул, тобто текстовий курсор перевести в рядок формул і приступити до її редагування.
  2. Натиснути F2, тобто активізувати комірку
  3. Подвійне клацання лівою кнопкою миші по комірці, що редагують

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

Кінець редагування, як і кінець введення

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

Оформлення таблиці. Форматування таблиці

Текстові дані за замовчуванням вирівнюються по лівому краю комірки, а числа і дати - по правому.

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

  1. Перед форматуванням необхідно виділити об'єкт, що підлягає форматуванню.
  2. Форматування таблиці здійснюється наступними способами:
  3.   

    2.1.Вкладка Главная→ Число .

        2.3.Контекстного меню, «Формат ячеек».

    ​Вибір числового формату

В Excel є можливість створення власного, призначеного для користувача числового формату.

Таблиця 3  . Числові формати

Щоб відображати числа

На вкладці

«Число» вибрати категорію

Кнопки панелі

інструментів

«Число»

Не використовуючи спеціальні засоби

Загальний

  

З роздільниками груп розрядів, з вирівняними розрядами і спеціальним виділенням для від'ємних чисел

Числовий

  

З вирівняними розрядами, символами валют і спеціальним виділенням для від'ємних чисел

Грошовий

З вирівняними розрядами та символами валют

Фінансовий

Як дати, час доби або час доби і дати одночасно

Дата

  

Як час доби

Час

  

В процентах

Процентний

З дробами

Дробовий

  

У науковому уявленні, у формі E+

Експоненціальний

  

Як текст або обробляти числа як текст

Текстовий

  

Як поштовий індекс, телефонний або табельний номер

Додатковий

  

 

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

У фінансовому форматі для зручності читання великі числа автоматично представляються з роздільником груп розрядів.

Процентний.

Експоненціальний (науковий) формат

В цьому форматі число завжди відображається з буквою E.

Таблиця 4  . Приклади застосування Експоненціального формата

Формат

Вміст

комірок

(рядок введення)

Відображення

результату в комірці

Пояснення

Експоненціальний

123456789

1,2346E+9

1,23456789*109

0,00000005

5Е-08

5*10-8

0,000008

8Е-06

8*10-6

12,345

1,23E+01

1,2345*101

Текстові формати

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

Формати Дата і Час

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

Перенесення тексту на декілька рядків всередині однієї комірки

1. Виділіть комірки, формат яких необхідно змінити.

2. В меню Вкладка Главная виберіть команду Число, а потім відкрийте вкладку Выравнивание 

3. В полі Отображение встановіть прапорець переносить по словам.

Зміна висоти рядка

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

Зміна ширини стовпця

Щоб стовпці мали однакову ширину необхідно виконати наступні дії:

Використання стилів

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

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

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

Шість атрибутів стилю відповідають шести вкладкам діалогового вікна Формат ячеек . Нижче перераховані атрибути форматування, які можна включити в стиль:

  1. Числове форматування;
  2. Вибір шрифту (тип, розмір і колір);
  3. Вирівнювання (вертикальне і горизонтальне);
  4. Рамки;
  5. Узори;
  6. Захист.

За замовчуванням всім коміркам призначається стиль Звичайний.

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

Таблиця 5  . Вбудовані стилі Excel

 

Назва

стилю

Опис

Приклади числового форматування

1.

Звичайний

Стандартний стиль Excel

1234

2.

Фінансовий*

Число з двома цифрами після коми

1 234,00

3.

Фінансовий [0]

Ціле число

1 234

4.

Грошовий*

Число з двома цифрами після коми і знаком грошової одиниці (грн.)

1234,00 грн.

5.

Грошовий [0]

Ціле число із знаком грошової одиниці

1234 грн.

6.

Процентний*

Ціле число із знаком відсотка

12%

* - Ці стилі можна призначити за допомогою клацання мишею на відповідних кнопках панелі інструментів Число.

Копіювання форматів

Для копіювання форматів з однієї комірки в іншу або в діапазон комірок використовується команда Правка→Специальная вставка. В діалоговому вікні, що з'явилося, виберіть перемикач Форматы.

Крім того, можна використовувати на панелі інструментів кнопку Формат по образцу .

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

Застосування автоматичного форматування

Щоб використовувати засіб автоматичного форматування  Еxcel, треба встановити табличний курсор на будь-який з елементів таблиці, яку потрібно відформатувати (межі таблиці Excel визначає автоматично). Потім виконати команду Файл - Параметры - Правописание натиснути кнопку Параметры автозамены. В Діалоговому віконці натиснути вкладку Автоформат при вводе  - поставити галочку - Включать в таблицу новые строки и столбцы, Автоматически в ходе работы.

Щоб використовувати засіб автоматичного форматування  Еxcel, треба встановити табличний курсор на будь-який з елементів таблиці, яку потрібно відформатувати (межі таблиці Excel визначає автоматично). Потім виконати  одну з команд на Вкладці Главная - Стили  - Условное форматирование або Форматировать как таблицу абоСтили ячеек.

Умовне форматування

Дуже часто користувачам Excel необхідно переформатувати комірки, виходячи з їх змісту.

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

Етапи форматування по заданій умові

3.1 Щоб в якості умови форматування використовувати значення виділених комірок, необхідно виконати наступні дії:

     a) Вибрати параметр значення;

     b) Вибрати операцію порівняння;

     c) в необхідне поле ввести потрібне значення .

3.2 Щоб в якості умови форматування використовувати формулу необхідно виконати наступні дії:

     a) Зліва з поля вибрати параметр формула;

     b) В полі справа ввести формулу.

Формула повинна приймати логічне значення ИСТИНА або ЛОЖЬ.

  1. Виділити комірки, які необхідно форматувати.
  2. Вибрати Условное форматирование.
  3. У вікні Условное форматирование із списку, що розкривається, вибрати одну з наступних опцій: Значення або Формула.
  1. Вибрати команду Формат.
  2. У вікні «Формат ячеек» вибрати тип шрифту, його колір, підкреслення, рамку і фон комірок або узори.
  3. Для додавання умови вибрати команду «а также» потім повторити кроки 3-5 вже для умови 2, умови 3.
  4. Коли все буде зроблено, клацніть на кнопці ОК.

Вибрані формати будуть застосовані, тільки якщо значення комірки відповідає поставленій умові або якщо формула приймає значення ИСТИНА.

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

Використання посилань на комірки в якості умови

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

Обчислення в електронних таблицях

Формули

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

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

Основне правило використання формул в програмі Ехсеl полягає в тому, що, якщо значення комірки дійсно залежить від інших елементів таблиці, завжди слід використовувати формулу, навіть якщо операцію легко виконати «усно»! Це гарантує, що подальше редагування таблиці не порушить її цілісності і правильності обчислень, що в ній виконуються.

 

Рисунок 6  . Вміст комірки.

Посилання на комірки

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

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

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

Посилання на комірку можна задати різними способами:

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

Абсолютні та відносні посилання

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

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

Відносна адресація. Наприклад, A1, C2:F10.

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

Абсолютна адресація. Наприклад, A$1 $A1 $A$1 $C$2:$F$10

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

$ - символ незмінності (абсолютності) елемента адреси комірки (стовпець або рядок, або стовпець і рядок)

Для зміни способу адресації при редагуванні формули треба виділити посилання на комірку і натиснути клавішу F4.

Введення формул

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

  1. Виберіть комірку, в яку потрібно ввести дані.
  2. Введіть знак рівності, натиснувши клавішу < = >.
  3. Клацніть мишею на комірці, ім'я якої ви хочете включити у формулу. Вказана комірка тут же буде виділена переривчастою рамкою ("крокуючими мурашками").
  4. Її ім'я з'явиться в комірці, що містить формулу, яка записується і відобразиться в рядку формул (ім'я потрібної комірки можна ввести і безпосередньо за допомогою клавіатури).
  5. Введіть потрібний оператор. Активною знову стане комірка, що містить формулу.
  6. Вкажіть наступну комірку, яку необхідно включити у формулу.
  7. Продовжуйте введення операторів і вказівку комірок до тих пір, поки не завершите створення формули.
  8. Для завершення введення формули натисніть клавішу <Enter>.

В комірці відобразиться результат обчислень. Клацнувши на ньому, можна побачити в рядку формул відповідний математичний вираз (див. Рис. 20).

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

Автоматизація введення

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

Автозаповнення числами

При роботі з числами використовується метод автозаповнення. В правому нижньому кутку рамки поточної комірки є чорний квадратик - це маркер заповнення (Ошибка! Источник ссылки не найден.). При наведенні на нього покажчик миші (він звичайно має вид товстого білого хреста) набуває форму тонкого чорного хрестика. Перетягування маркера заповнення розглядається як операція «розмноження» вмісту комірки в горизонтальному або вертикальному напрямі.

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

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

Переміщення або копіювання формули

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

  1. Виберіть комірку, що містить формулу, яку необхідно скопіювати або перемістити.
  2. Встановіть покажчик на рамку виділення і натисніть кнопку миші.
  3. Для переміщення комірки перетягніть виділення в необхідну область, утримуючи натиснутою кнопку миші. Microsoft Excel замінить всі існуючі дані в області, що вставляється.

Щоб скопіювати комірку, при перетягуванні утримуйте натиснутою клавішу CTRL.

Копіювання вмісту комірок

Копіювання і переміщення комірок в програмі Ехсеl можна здійснювати методом перетягування або через буфер обміну.

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

Метод перетягування

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

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

Для копіювання (переміщення) необхідно:

  1. Виділити діапазон для копіювання (вирізання).
  2. Дати команду, щоб помістити його в буфер обміну на Вкладці Главная - Буфер обмена → Копировать (Вырезать).
  3. Визначити місце - куди копіювати (переміщати) шляхом активізації комірки, яка відповідає верхньому лівому куту діапазону, поміщеного в буфер обміну, або шляхом виділення діапазону, який за розмірами в точності рівний тому, що копіюється (переміщується)
  4. Дати команду вставити: на Вкладці Главная →Вставить

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

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

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

Копіювання (переміщення) методом протягування виконується так:

  1. Виділити комірки, які необхідно копіювати (перемістити).
  2. Для копіювання натиснути кнопку миші + CTRL і перетягнути в місце куди копіювати
  3. Для переміщення виділену область перетягніть в необхідну область таблиці.

Microsoft Excel замінить всі існуючі дані в області, що вставляється.

Порядок виконання дій у формулах

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

Спочатку виконуються операції в порядку спадання пріоритетів.

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

Для зміни порядку виконання операцій використовуються дужки ().

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

Таблиця 6  . Пріоритети виконання операцій

Пріоритет

Оператор

Опис

7

= < > <= >= <>

Порівняння:

6

&

Об'єднання двох текстових рядків в один.

5

+ і -

Додавання і віднімання.

4

* і /

Множення і ділення
3

^

Піднесення до степеню

2

%

Відсоток
1

-

Унарний мінус (наприклад -1).

0

: (двокрапка)

(пропуск)

, (кома)

Оператори посилань

 

5.4.2 Перетворення операндів і аргументів у формулах

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

Таблиця 7   Приклади перетворення аргументів у формулах

Формула

Результат

Пояснення

="1"+"2"

3

При використанні знака плюс (+), передбачається, що операнди є числами.

Навіть якщо взяти числа в лапки ("1" і "2" - текстові значення), Microsoft Excel автоматично перетворить їх в числа.

=1+"$4.00"

5

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

="6/1/2001"-"5/1/2001"

1

Microsoft Excel інтерпретує текст як дати, записані у форматі "dd.mm.yy", а потім перетворює їх в числа для обчислення кількості днів між цими двома датами.

=КОРЕНЬ("8+1")

#ЗНАЧ!

Текстове значення "8+1" не перетвориться в число.

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

"9" або "8"+"1"; тоді формула виконає перетворення тексту в число і поверне в якості результату 3.

="A-"&ИСТИНА

A-ИСТИНА

Коли операнди повинні бути текстовими значеннями,

виконується перетворення чисел і логічних значень (ИСТИНА і ЛОЖЬ) в текст.

 

Перевірка формул

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

  1. Виберіть в меню Файл→Параметры.
  2. Клацніть на вкладці Дополнительно, встановіть прапорець Показывать формулы, а не их значения.
  3. Клацніть на кнопці ОК.

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

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

Здійснювати перевірку робочих листів можна і за допомогою спеціальних вбудованих засобів Excel.

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

Для перевірки залежності виконайте наступні дії:

  1. Виберіть комірку, залежності якої хочете відстежити.
  2. Виконайте команду Вкладка Формулы - Зависимости формул→Влияющие ячейки .
  3. В тому підменю, що з'явилося виберіть потрібну опцію: Влияющие ячейки, Зависимые ячейки або Проверка наличия ошибок та таке інш.

На екрані відобразяться відповідні стрілки залежності.

Підсумкові обчислення

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

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

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

Наприклад, можливе обчислення:

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

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

Рисунок 7   Діалогове вікно вбудованої функції.

Обчислення суми

Будь-який користувач може використовувати в Excel функції - спеціальні формули, вбудовані в додаток. Завдяки функціям можна значно спростити складні математичні вирази.

Наприклад, така функція, як СУММ, дозволяє компактно записувати довгі ряди для обчислення суми. Припустимо, ви маєте наступну формулу: =С8+С9+С10+С11+С12

Замість неї можна записати наступний вираз і отримати той же результат:

=СУММ(С8:С12)

Найтиповіша з функцій, призначених для підсумкових обчислень, - це функція обчислення суми  (СУММ).

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

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

5.4.3 Використання функцій для обчислення значень

Функції - наперед визначені формули, які виконують обчислення по заданих величинах, які називають аргументами, і у вказаному порядку. Наприклад, функція СУММ підсумовує значення в діапазоні комірок, а функція ППЛАТ обчислює величину виплати за один період річної ренти на основі постійних виплат і постійної процентної ставки.

Аргументи.  Список аргументів може складатися з чисел, тексту, логічних величин (наприклад, ИСТИНА або ЛОЖЬ), масивів, значень помилок (наприклад #Н/Д) або посилань. Необхідно стежити за відповідністю типів аргументів. Крім того, аргументи можуть бути як константами, так і формулами. Ці формули, у свою чергу, можуть містити інші функції.

Структура

=<Имя функции>(Аргумент1;Аргумент2;…АргументN)

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

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

Рисунок 8  .Структура функції.

Використання майстра функції

Майстер функцій Excel (кнопка на стандартній панелі інструментів) полегшує процес введення в робочий лист потрібних визначених формул.

Для введення функції в комірку треба виконати наступні дії:

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

  1. Вибрати комірку, в яку потрібно помістити формулу.
  2. Вибрати в меню Главная → Редактирование - Сумма - Другие функции, або клацнути кнопку майстра функцій .
  3. На екрані з'явиться діалогове вікно майстра функцій, яке містить список 10 функцій, що недавно використовувалися. Інші категорії функцій будуть перераховані у відповідному списку.
  4. Вибрати в списку Категория потрібну опцію.
  5. В списку Функция будуть перераховані всі функції даного типу. Для ознайомлення зі описом функції клацніть на її імені. В нижній частині діалогового вікна майстра відобразиться потрібна інформація. У списку Функция клацніть на імені потрібної функції.
  6. Клацніть на кнопці ОК. Під рядком формул з'явиться палітра формул, в якій будуть відображені функції.
  7. В якості аргументів можуть виступати константи, внесені в робочий лист величини, посилання на комірки, діапазони і т.інш. Деякі аргументи повинні бути обов'язково вказані після імені функції. Їх назви виводяться в палітрі формул напівжирним шрифтом. Інші аргументи у функції можуть не вказуватися. Їх назви виводяться звичайним шрифтом. Програма електронних таблиць намагається "вгадати", які саме аргументи повинні бути використані у функції. Свої припущення Excel також відображає в палітрі формул.
  8. Введіть значення всіх необхідних аргументів.
  9. Натисніть клавішу <Enter> або клацніть на кнопці ОК(Готово).

Створена формула буде занесена в робочий лист в поточну комірку.

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

Таблиця 8   Статистичні функції

ЛИНЕЙН

Повертає параметри лінійного тренду

 

МАКС

Повертає максимальне значення із списку аргументів.

 

МАКСА

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

МИН

Повертає мінімальне значення із списку аргументів

МИНА

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

НАИБОЛЬШИЙ

Повертає k-те найбільше значення з множини даних

НАИМЕНЬШИЙ

Повертає k-те найменше значення в множині даних

СРЗНАЧ

Повертає середнє арифметичне аргументів

СРЗНАЧА

Повертає середнє арифметичне аргументів, враховуючи числа, текст і логічні значення.

СЧЁТ

Підраховує кількість чисел в списку аргументів

СЧЁТЗ

Підраховує кількість значень в списку аргументів

Функція роботи з датою і часом

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

Наприклад, якщо вимагається використати у формулі поточну дату, скористайтеся функцією СЕГОДНЯ, яка повертає поточну дату по системному годиннику.

Таблиця 9   Функції для роботи з датою та часом

ВРЕМЗНАЧ

Перетворює час з текстового формату в числовий формат

ВРЕМЯ

Повертає заданий час в числовому форматі

ГОД

Перетворює дату в числовому форматі за рік

ДАТА

Повертає задану дату в числовому форматі Microsoft Excel

ДАТАЗНАЧ

Перетворює дату з текстового формату в числовий

ДАТАМЕС

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

ДЕНЬ

Перетворює дату в числовому форматі в день місяця

ДЕНЬНЕД

Перетворює дату в числовому форматі в день тижня

ДНЕЙ360

Обчислює кількість днів між двома датами на основі 360-денного року

МЕСЯЦ

Перетворює дату в числовому форматі в місяці

СЕГОДНЯ

Повертає числовий формат поточної дати

Математичні функції

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

Таблиця 10   Математичні функції

SIN

Повертає синус заданого кута
TAN Повертає тангенс числа

КОРЕНЬ

Повертає позитивне значення квадратного кореня

НЕЧЁТ

Округляє число до найближчого непарного цілого

ОКРВВЕРХ

Округляє число до найближчого цілого або до найближчого кратного вказаному значенню

ОСТАТ

Повертає частку від ділення

ОТБР

Відкидає дробову частину числа

ПИ

Повертає число «Пі»

ПРОИЗВЕД

Повертає добуток аргументів

ПРОМЕЖУТОЧНЫЕ ИТОГИ

Повертає проміжний підсумок в списку або базі даних

СЛЧИС

Повертає випадкове число в інтервалі від 0 до 1

СТЕПЕНЬ

Повертає результат піднесення числа до степеня

СУММ

 Підсумовує аргументи

СУММЕСЛИ

Підсумовує комірки, що задовольняють задану умову

СУММКВ

Повертає суму квадратів аргументів

СЧЁТЕСЛИ

Підраховує кількість непорожніх комірок, що задовольняють задану умову всередині діапазону

ЦЕЛОЕ

Округляє число до найближчого меншого цілого

ЧЁТН

Округляє число до найближчого парного цілого

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

Синтаксис СУММ(число1;число2; ...)

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

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

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

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

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

СУММЕСЛИ

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

Синтаксис

СУММЕСЛИ(интервал; критерий;сумм_интервал)

Интервал - це інтервал обчислюваних комірок.

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

Наприклад, критерій може бути виражений як 32, "32", ">32", "яблука".

Сумм_интервал - це фактичні комірки для підсумовування.

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

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

Вкладені функції

Функції можуть використовуватися як аргументи в інших функціях.

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

Повернення правильного значення.

Якщо функція використовується як аргумент або є вкладеною функцією, то вона повинна повертати аргументу значення, що відповідає типу аргументу. Наприклад, якщо аргумент приймає значення ИСТИНА або ЛОЖЬ, то і вкладена функція повинна повертати значення ИСТИНА або ЛОЖЬ.

Якщо функція повертає значення іншого типу, буде відображена помилка #ЗНАЧ!

Результат порівняння повинен бути логічною величиною (ИСТИНА або ЛОЖЬ), оскільки це необхідний тип для першого аргументу функції ЕСЛИ.

У формулах можна використовувати до семи рівнів вкладення функцій.

Коли «функція Б» є аргументом «функції А», то «функція Б» вважається другим рівнем вкладення. Якщо в «функції Б» міститься в якості аргументу «функція В», то «функція В» вважатиметься третім рівнем вкладення функцій.

Для продовження введення аргументів в «функцію А» вкажіть її ім'я в рядку формул.

Щоб використовувати функцію в якості аргументу, можна скористатися панеллю формул.

Щоб зробити функцію СРЗНАЧ аргументом функції ЕСЛИ, натисніть значение_если_истина в полі введення, натисніть стрілку вниз на панелі формул в меню Функция і виберіть СРЗНАЧ, а в полі значение_если_ложь аналогічно виберіть функцію СУММ.

Щоб перемикатися між функціями на панелі формул, натисніть кнопку миші на імені формули в списку формул.

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

Логічні функції перевіряють комірки або їх діапазони на певну умову і повертають значення Истина, якщо умова виконується, і ЛОЖЬ, якщо не виконується.

В Табл.11 даний короткий опис логічних функцій.

Таблиця 11   Логічні функції.

Функція

Синтаксис

Опис

ЕСЛИ

=ЕСЛИ(<Условие>; Зн_Истина;Зн_Ложь)

Повертає одне значення, якщо задана умова при обчисленні дає значення ИСТИНА, і інше значення, якщо ЛОЖЬ.

И

=И(логич_знач1; логич_знач2;...)

Повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення ЛОЖЬ.

Или

=ИЛИ(логич_знач1; логич_знач2; ...)

Повертає ИСТИНА, якщо хоча б один з аргументів має значення ИСТИНА; повертає ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.

Истина

=Истина()

Повертає логічне значення ИСТИНА.

ложь

=ложь()

Повертає логічне значення ЛОЖЬ.

Не

=НЕ(логическое_значение)

Змінює на протилежне логічне значення свого аргументу.

 

Функція ЕСЛИ

Функція ЕСЛИ використовується при перевірці умов для значень і формул.

Повертає одне значення, якщо задана умова при обчисленні дає значення ИСТИНА, та інше значення, якщо ЛОЖЬ.

Синтаксис ЕСЛИ(<Условие>; Зн_Истина;Зн_Ложь)

Де

<Условие> (Лог_выражение)- це будь-яке значення або вираз, що приймає значення ИСТИНА або ЛОЖЬ.

Зн_Истина - це значення, яке повертається, якщо <Условие> рівне ИСТИНА.

Зн_Ложь- це значення, яке повертається, якщо <Условие> рівне ЛОЖЬ.

Зн_Истина, Зн_Ложь можуть бути: число, посилання, ім'я, функція, текстова константа..


Функція И

Повертає значення ИСТИНА, якщо всі аргументи мають значення ИСТИНА; повертає значення ЛОЖЬ, якщо хоча б один аргумент має значення ЛОЖЬ.

Синтаксис

И(логическое_значение1; логическое_значение2; ...)

Логическое_значение1, логическое_значение2 ... - це від 1 до 30 умов, що перевіряються, і які можуть мати значення або ИСТИНА, або ЛОЖЬ.

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

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

· Якщо вказаний інтервал не містить логічних значень, то И повертає значення помилки #ЗНАЧ!.

Функція ИЛИ

Повертає ИСТИНА, якщо хоча б один з аргументів має значення ИСТИНА; повертає ЛОЖЬ, якщо всі аргументи мають значення ЛОЖЬ.

Синтаксис

ИЛИ(логическое_значение1;логическое_значение2; ...)

Логическое_значение1, логическое_значение2 ...- це від 1 до 30 умов, що перевіряються, і які можуть мати значення або ИСТИНА, або ЛОЖЬ.

Для того, щоб ввести формулу масиву, натисніть CTRL+SHIFT+ENTER в Microsoft Excel для Windows.

Функція НЕ

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

Синтаксис НЕ(логическое_значение)

Логическое_значение - це значення або вираз, який при обчисленні дає ИСТИНА або ЛОЖЬ.

Якщо логическое_значение має значення ЛОЖЬ, то функція НЕ повертає значення ИСТИНА; якщо логическое_значение має значення ИСТИНА, то функція НЕ повертає значення ЛОЖЬ.

Приклади

НЕ(ЛОЖЬ) дорівнює ИСТИНА

НЕ(1+1=2) дорівнює ЛОЖЬ

Приклади формул, що часто використовуються


Функції перевірку типу

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

Кожна з цих функцій перевіряє тип значення і повертає значення ИСТИНА або ЛОЖЬ залежно від типу.


Значение - це значення, що перевіряється.

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

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

Комбінуючи ці функції з функцією ЕСЛИ, можна локалізувати помилки у формулах (див. наступні приклади).

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

Функція ВЫБОР

Використовує номер_индекса, щоб вибрати і повернути значення зі списку аргументів-значень.

Функція ВЫБОР використовується, щоб вибрати одне значення зі списку, в якому може бути до 29 значень.

Синтаксис ВЫБОР(номер_индекса;значение1;значение2;ј)

Номер_индекса - це номер аргументу-значення, що обирається.

Номер_индекса повинен бути числом від 1 до 29, формулою або посиланням на комірку, що містить число від 1 до 29.

Значение1, значение2, ј - це від 1 до 29 аргументів-значень, з яких ВЫБОР, використовуючи номер_индекса, вибирає значення або дію, що виконується.

Аргументи можуть бути числами, посиланнями на комірки, іменами, формулами, функціями або текстами.

Помилка у формулах

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

Нижче наведений список помилок, а також причини їх виникнення.

Таблиця 12   Найчастіші повідомлення про помилки

№ помилки

Можлива причина

Пропоновані дії

####

1.

Комірка містить число, дату або час, число знаків яких більше ширини стовпця

Збільшити ширину стовпця

#ПУСТО!

2.

Помилкове посилання на комірку або діапазон

Перевірте, чи не допущені помилки при введенні посилань на діапазони комірок
#ДЕЛ/0!

3.

При обчисленні формули відбувається ділення на нуль

Виправіть формулу або введіть дані в комірки, на які є посилання в знаменнику

#ЗНАЧ!

4.

Замість числового або логічного (ИСТИНА або ЛОЖЬ) значення введений текст, Microsoft Excel не може перетворити його на потрібний тип даних.

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

 

Після введення або редагування формули масиву натискається клавіша ENTER

Для редагування формули вкажіть комірку або діапазон комірок, що містять формулу масиву, натисніть клавішу F2, потім клавіші CTRL+SHIFT+ENTER.

#ССЫЛКА!

5.

Використовується неприпустиме посилання на комірку (наприклад, видалена комірку, на яку є посилання)

Змініть формулу або, якщо помилка з'явилася одразу після видалення або вставки скопійованих комірок, відновіть колишній вміст комірок за допомогою кнопки «Отменить» стандартної панелі інструментів

#ИМЯ?

6.

Ім'я, що використовується, було видалено або не було визначено

Визначте ім'я. Виберіть в меню Вставка команду Імя, а потім -команду Создать. Крім того, команда Создать

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

Є помилка в написанні імені.

Виправіть написання імені.

Щоб вставити правильне ім'я у формулу, виділіть ім'я в рядку формул, виберіть в меню Вставка команду Імя, а потім - команду Вставить.

На екрані з'явиться діалогове вікно Вставка имени.

Виділіть потрібне ім'я і натисніть кнопку OK

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

Помістіть текст формули в подвійні лапки.

Наприклад, в наступній формулі правильно об'єднується текстовий фрагмент «Всього:» зі значенням комірки B50:="Всего: "&B50

В посиланні на діапазон комірок пропущений знак двокрапка (:).

Виправте формулу так, щоб у всіх посиланнях на діапазон комірок використовувався знак двокрапка (:), наприклад: СУММ(A1:C10).

#ЧИСЛО!

7.

Можливо, некоректне використання чисел у формулі або результатом обчислення є дуже велике або дуже мале число

Перевірте правильність

аргументів, що використовуються у функції і змініть формулу так, щоб в результаті її обчислення виходило число, що потрапляє в наступний діапазон: -1*10307 і 1*10307

#Н/Д «Неопределенные Данные».

8.

Найвірогідніша причина - заданий неприпустимий аргумент

Задайте правильний аргумент і його розміщення у функції

 

Рекомендації при роботі з формулами

  1. Перевірте, що всі дужки відкриваються і закриваються. При створенні формули Microsoft Excel виділятиме круглі дужки, що вводяться.
  2. Перевірте правильність використання оператора діапазону при посиланні на групу комірок. Якщо є посилання на групу комірок, для розділення посилань на першу і другу комірки діапазону скористайтеся двокрапкою (:).
  3. Перевірте, що введені всі необхідні аргументи. Для роботи деяких функцій необхідно ввести аргументи. Крім того, перевірте, що введено не дуже багато аргументів.
  4. У функцію можна вкласти не більше семи функцій.
  5. Якщо перший символ в імені книги або листа не є буквою, необхідно укласти ім'я в одинарні лапки.
  6. Перевірте, що в кожному зовнішньому посиланні вказано ім'я книги і повний шлях до неї.
  7. Не змінюйте формат чисел, введених у формули. Наприклад, навіть якщо у формулу необхідно ввести 1000 р., то введіть число 1000. Щоб змінити формат результату, що виводиться формулою, виберіть команду Ячейки в меню Формат, а потім - вкладку Число. Після чого виберіть необхідний формат.

ТИП.ОШИБКИ

Повертає номер, відповідний одному з можливих помилкових значень в Microsoft Excel. Функція ТИП.ОШИБКИ може бути використана у функції ЕСЛИ для перевірки значення помилки і повернення рядка тексту замість відповідного значення помилки.

Синтаксис ТИП.ОШИБКИ(значение_ошибки)

Значение_ошибки - це значення помилки, для якого визначається номер.

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

Значення помилки

Номер, що поверта-ється

Значення помилки

Номер, що поверта-ється

Значення

помилки

Номер, що поверта-ється

#ПУСТО!

1

#ССЫЛКА!

4

#Н/Д

7

#ДЕЛ/0!

2

#ИМЯ?

5

Будь-яке інше

#Н/Д

#ЗНАЧ!

3

#ЧИСЛО!

6

    

 

 

Конспект лекції Висновки
© 2017 СумГУ
created with Lectur'EDbeta