Тема: MS Excel. Робота з даними – пошук і заміна. Перевірка орфографії. Перевірка формул

Лабораторна робота №5

1. Робота з даними – пошук і заміна даних.

1.1. В таблиці можна робити коригування даних через Пошук і заміну даних.

Відкрити таблицю Зарплата, поставити курсор в комірку С7 і через вкладку Главная/HOME виконати Найти/Find для колонки С для значення 14

1.2. Відкоригувати вкладку Заменить на 19. Виконати заміну знайденого значення.

2. Виділення даних

2.1. Виділення групи даних потрібно для коригування або форматування цих даних.

В Excel існують наступні можливості виділення даних – вкладка HOME група Editing команда Выделение группы ячеек…/Go to…

Виконати різні варіанти виділення даних.

2.2. Ці ж команди можна виконати через команду

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

3. Перевірка орфографії.

3.1. Перевірити орфографію можна через вкладку Reviews, наприклад

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

Методична частина: Перевірка формул має декілька можливостей – перевірка впливових та залежних комірок, перегляд формул, покрокове виконання формули, вирахування контрольних значень.

Нижче наведений перелік типових помилок при створенні формул:

Таблиця 5.1. Типові помилки при створенні формул

Перевірити, що …	Пояснення
Кожна формула починається зі знака рівності (=)	Якщо знак рівності вилучити, уведені значення можуть бути відображені як текст або дата.
Усі відкриваючі й закриваючі дужки погоджені	Переконайтеся, що в кожної дужки є відповідна їй пари. Щоб функція у формулі працювала правильно, важливо, щоб кожна дужка стояла на своєму місці. Наприклад, формула =ЕСЛИ(B5<0);"Невірно";B5*1,05) працювати не буде, тому що в ній дві закриваючі й тільки одна відкриваюча дужка. Правильна формула буде виглядати так: =ЕСЛИ(B5<0;"Невірно";B5*1,05).
Для вказування діапазону використовується двокрапка	При вказівці посилання на діапазон використовуйте двокрапку (:) у якості роздільника між першою й останньою комірками діапазону. Наприклад: A1:A5.
Уведені всі необхідні аргументи	Деяким функціям потрібні аргументи, тоді як інші функції аргументів не потребують. Крім того, переконайтеся, що ви не ввели занадто багато аргументів.
Уведені аргументи правильного типу	Для деякий функцій, наприклад SUM, потрібні числові аргументи. Інші функції, вимагають текстового значення. При використання як аргументу даних невірного типу можна одержати неправильні результати або помилку.
Кількість вкладених функцій не перевищує 64	Усередині однієї функції можна ввести (або вкласти) не більш 64 рівнів функцій
Імена аркушів в посиланнях включені в одинарні лапки	Якщо у формулі є посилання на комірки інших аркушів або книг, імена яких містять не тільки літери, такі імена необхідно включити в одинарні лапки (').
Посилаючись на аркуш у формулі, слід помістити після його імені знак оклику (!).	Наприклад, щоб повернути значення комірки D3 аркуша з іменем "Дані за квартал" у тій же книзі, вкажіть в формулі ='Дані за квартал'!D3.
Включений шлях до зовнішніх книг	Переконаєтеся, що кожне зовнішнє посилання містить ім'я книги й шлях до неї. Посилання на книгу містить у собі ім'я цієї книги й повинна бути включене у квадратні дужки ([]). Крім того, у посиланні повинне бути зазначене ім'я аркуша в книзі. Наприклад, формула з посиланням на комірки з A1 по A8 на аркушу з іменем "Продажі" у книзі (у даний момент відкритої в Excel) з іменем Кв2 Операції.xlsx, буде виглядати так: =[Кв2 Операції.xlsx]Продажі!A1:A8. Якщо книга, на яку потрібно послатися, не відкрита в Excel, посилання на неї все-таки можна включити у формулу для чого потрібно вказати повний шлях до файлу, як у наступному прикладі: =COUNT('C:\Мої документи\[Кв2 Операції.xlsx]Продажі'!A1:A8). Ця формула повертає число рядків у діапазоні, що включає комірки з A1 по A8 в іншій книзі. Примітка Якщо повний шлях містить проміжки, як у попередньому прикладі, його необхідно обмежити одинарними лапками (на початку шляху й після імені аркуша, перед знаком оклику).
Числа введені без форматування	Не форматуйте числа, які вводите у формулах. Наприклад, якщо вам потрібно ввести значення ₴1 000, уведіть у формулу просто 1000.

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

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

Таблиця 4.2 Перелік помилок в формулах EXCEL

Помилка	Опис
#####	Ця помилка з'являється в тому випадку, якщо ширини стовпця недостатньо, щоб показати всі знаки в комірці, або якщо комірка містить негативні значення дати або часу. Так, при обчисленні формули, яка віднімає більш пізню дату з більш ранньої, наприклад =15.06.2008-01.07.2008, вийде негативне значення дати.
#ДЕЛ/0! (#DIV/0!)	Ця помилка з'являється в тому випадку, якщо число ділиться на нуль (0) або на комірку, у якій немає значення.
#ЗНАЧ! (#VALUE!)	Ця помилка з'являється в тому випадку, якщо формула містить комірки з різними типами даних. Якщо включена перевірка помилок у формулах, при наведенні покажчика миші на смарт-тег з'явиться спливаюча підказка "Значення, використовуване у формулі, має неправильний тип даних".
#ИМЯ? (#NAME?)	Ця помилка з'являється в тому випадку, якщо текст у формулі не вдається розпізнати. Наприклад, ім'я діапазону або функції може мати невірне написання.
#Н/Д!(#N/U!)	Ця помилка з'являється в тому випадку, якщо значення для функції або формули недоступно.
#ПУСТО! (#NULL!)	Ця помилка з'являється в тому випадку, якщо зазначене перетинання двох областей, які насправді не перетинаються. Оператором перетинання служить пробіл, який розділяє посилання у формулі. Наприклад, області A1:A2 і C3:C5 не перетинаються, і якщо ввести формулу =СУМ(A1:A2 C3:C5), буде показана помилка #ПУСТО!.
#ССЫЛКА! (#REF!)	Ця помилка з'являється в тому випадку, якщо посилання на комірку є невірним. Наприклад, ви могли вилучити комірку, на які посилалися інші формули.
#ЧИСЛО! (#NUM!)	Ця помилка з'являється в тому випадку, якщо формула або функція містить неприпустимі числові значення.

4.1. Налаштування перевірки формул і виправлення розповсюджених помилок формул на аркуші

4.1.1. Відкрийте вкладку Файл.

4.1.2. Натисніть кнопку Параметри й виберіть категорію Формулы.

4.1.3. Переконаєтеся, що в області Контроль ошибок включений прапорець Включить фоновый поиск ошибок і в розділі Правила контроля ошибок. включіть потрібні прапорці

4.1.4. Щоб змінити колір трикутника, яким позначаються помилки, виберіть потрібний колір у поле Цвет индикаторов ошибок. Натисніть кнопку "ОК", щоб закрити діалогове вікно Параметри Excel.

4.1.5. Щоб виправити помилку на аркуші, виберіть комірку із трикутником у лівому верхньому куті.

4.1.6. Натисніть, на кнопку Контроль помилок, що з'явився поруч із коміркою, і виберіть потрібний пункт. Доступні команди залежать від типу помилки. Перший пункт містить опис помилки.

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

4.2. Перевірка залежних комірок

Перевірки виконуються за допомогою команд вкладки Formulas команда Formula Auditing

4.2.1. Вибрати лист 01.

4.2.2. Встати в комірку D7, натиснути на кнопку Trace precedents (Влияющие ячейки) перевірити в формулі вхідні комірки.

4.2.3. Встати на комірку F11, натиснути на кнопку для перевірки – які комірки використовуються для розрахунку в цій комірці. Результат буде мати такий вигляд

4.2.4. Убрати всі стрілки кнопкою .

4.3. Камеральна перевірка формул

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

4.3.1. Натиснути клавіші Ctrl + ~, з’явиться відображення формул

4.3.2. Якщо потрібна камеральна перевірка створених формул лист можна віддрукувати, попередньо вказавши режим друку з ідентифікаторами рядків та колонок – FileðPage SetUpð-SheetðPrintðRow and column header.

4.3.3. Встановити цей режим

4.3.4. Зробити попередній перегляд отриманого результату

4.3.5. Відмінити режим перегляду формул – знову натиснути клавіші Ctrl + ~ та знявши режим PrintðRow and column header.

4.4. Використання тегів помилок та покрокова перевірка формули

4.4.1. Поміняти формат комірки C8 на текстовий – з’явиться зображення зеленого трикутника, наведіть курсор та розкрийте список біля знаку!

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

4.4.3. Виправте значення комірки С7 - введіть значення „ааа”, в формулах з’являться ознаки помилок.

4.4.4. Виконати перевірку формули покроково, для чого виділити комірку D7 і виконати команду Вычислить формулу.

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

4.4.5. Нижче наведений приклад виявлення невідомої помилки після виконання трасування помилки (формула поставлена в рядку з шапкою)

Кінець роботи

Дата добавления: 2015-11-14