Текстові функції. Функції дати і часу в Excel 2003

Опис документу:
Теоретичний матеріал (методичний посібник до уроку)

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

Перегляд
матеріалу
Отримати код Поділитися

План уроку

Тема № 2. Обробка табличної інформації.

Тема уроку: Текстові функції. Функції дати і часу в Excel.

Мета уроку: Ознайомити з використанням текстових функцій та функцій дати і часу в Excel. Розглянути приклади використання текстових функцій та функцій дати і часу, їх можливості та призначення. Розвивати логічне мислення, пам’ять, увагу, самоконтроль і вміння конспектувати; Виховувати інтерес до предмету та наполегливість у навчанні, інформаційну культуру, акуратність, дисциплінованість, посидючість.

Метод проведення уроку: лекція.

Тип уроку: пояснювально – демонстраційний.

Обладнання: зошит, підручник, опорний конспект, наочний матеріал, роздатковий матеріал, комп’ютер.

Хід уроку

І. Оргмомент.

ІІ. Актуалізація опорних знань учнів.

ІІІ. Оголошення теми та мети уроку.

ІV. Вивчення нового матеріалу:

ТЕКСТОВІ ФУНКЦІЇ

Текст в Excel - це будь-яка сукупність буквенно – цифрових символів, які не є числовим значенням, значенням дати або часу або формулою. Слова, назви, мітки стовпців - все це текстові значення; текстовими значеннями є також значення комірок, які мають Текстовий формат або перед якими стоїть апостроф (). Текстові значення називають також рядками.

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

Для роботи з текстовими функціями в Excel використовується «Мастер функций», який можна викликати за допомогою команди Головного меню - Вставка \ Функция…, або за допомогою кнопки «Вставка функции» яка розташована поряд з Рядком формул вікна MS Excel. Після виклику вікна «Мастер функций» потрібно у ньому ж обрати категорію «Текстовые» (див. мал. 1) після чого у відповідному полі буде відображено всі текстові функції Excel, їх усього 24, значення яких можна дізнатися за допомогою гіперпосилання «Справка по этой функции», що знаходиться у цьому ж вікні, тим самим викликавши вікно довідки MS Excel.

Розглянемо Текстові функції Excel, які найбільш часто використовуються.

Функція «ТЕКСТ»:

Функція «ТЕКСТ» (TEXT) перетворює число в текстовий рядок із заданим форматом. Синтаксис:

=ТЕКСТ(значення;формат)

Аргумент значення може бути будь-яким числом, формулою або посиланням на комірку. Аргумент формат визначає, в якому вигляді відображається рядок, що повертається. Для задання необхідного формату можна використовувати будь-який з символів форматування за винятком зірочки. Використання формату «Общий» не допускається. Наприклад, наступна формула повертає текстовий рядок 25,25:

=ТЕКСТ(101/4;"0,00")

Функція «РУБЛЬ»:

Функція «РУБЛЬ» (DOLLAR) перетворює число в рядок. Проте «РУБЛЬ» повертає рядок в грошовому форматі із заданим числом десяткових знаків. Синтаксис:

=РУБЛЬ(число;число_знаків)

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

Функція «ДЛСТР»:

Функція «ДЛСТР» (LEN) повертає кількість символів в текстовому рядку і має наступний синтаксис:

=ДЛСТР(текст)

Аргумент текст має бути рядком символів, виділений подвійними лапками, або посиланням на комірку. Наприклад, наступна формула повертає значення 6:

=ДЛСТР("голова")

Функція «ДЛСТР» повертає довжину тексту, що відображається, або значення, а не значення комірки, що зберігається. Крім того, вона ігнорує незначущі нулі.

Функція «СИМВОЛ» і «КОДСИМВ»:

Будь-який комп'ютер для представлення символів використовує числові коди. Найбільш поширеною системою кодування символів є ASCІІ. У цій системі цифри, букви і інші символи представлені числами від 0 до 127 (255). Функції «СИМВОЛ» (CHAR) і «КОДСИМВ» (CODE) якраз і мають справу з кодами ASCII. Функція «СИМВОЛ» повертає символ, який відповідає заданому числовому коду ASCII, а функція «КОДСИМВ» повертає код ASCII для першого символу її аргументу. Синтаксис функцій:

=СИМВОЛ(число)

=КОДСИМВ(текст)

Якщо в якості аргументу Текст вводиться символ, обов'язково треба заключити його в подвійні лапки: інакше Excel поверне помилкове значення.

Функції «СЖПРОБЕЛЫ» і «ПЕЧСИМВ»:

Часто початкові і кінцеві пропуски не дозволяють правильно відсортувати значення в робочому аркуші або базі даних. Якщо ви використовуєте текстові функції для роботи з текстами робочого аркуша, зайві пропуски можуть заважати правильній роботі формул. Функція «СЖПРОБЕЛЫ» (TRIM) видаляє початкові і кінцеві пропуски з рядка, залишаючи лише по одному пропуску між словами. Синтаксис:

=СЖПРОБЕЛЫ(текст)

Функція «ПЕЧСИМВ» (CLEAN) аналогічна функції «СЖПРОБЕЛЫ» за винятком того, що вона видаляє всі символи які не друкуються. Функція «ПЕЧСИМВ» особливо корисна при імпорті даних з інших програм, оскільки деякі імпортовані значення можуть містити символи, що не друкуються. Ці символи можуть виявлятися на робочих листах у вигляді невеликих квадратів або вертикальних рисок. Функція «ПЕЧСИМВ» дозволяє видалити символи, що не друкуються з таких даних. Синтаксис:

=ПЕЧСИМВ(текст)

Функція «СОВПАД»:

Функція «СОВПАД» (EXACT) порівнює два рядки тексту на повну ідентичність з врахуванням регістра букв. Відмінність у форматуванні ігнорується. Синтаксис:

=СОВПАД(текст1;текст2)

Якщо аргументи текст1 і текст2 ідентичні з врахуванням регістра букв, функція повертає значення ІСТИНА, інакше - БРЕХНЯ. Аргументи текст1 і текст2 мають бути рядками символів, у подвійних лапках, або посиланнями на комірки, в яких міститься текст.

Функції «ПРОПИСН», «СТРОЧН» і «ПРОПНАЧ»:

У Excel є три функції, що дозволяють змінювати регістр букв в текстових рядках: «ПРОПИСН» (UPPER), «СТРОЧН» (LOWER) і «ПРОПНАЧ» (PROPER). Функція «ПРОПИСН» перетворить всі букви текстового рядка в прописні, а «СТРОЧН» - в рядкові. Функція «ПРОПНАЧ» замінює прописними першу букву в кожному слові і всі букви, наступні безпосередньо за символами, відмінними від букв; всі інші букви перетворюються в рядкові. Ці функції мають наступний синтаксис:

=ПРОПИСН(текст)

=СТРОЧН(текст)

=ПРОПНАЧ(текст)

При роботі з вже існуючими даними досить часто виникає ситуація, коли потрібно модифікувати самі вихідні значення, до яких застосовуються текстові функції. Можна ввести функцію в ті ж самі комірки, де знаходяться ці значення, оскільки введені формули замінять їх. Але можна створити тимчасові формули з текстовою функцією у вільних комірках в тому ж самому рядку і скопіювати результат в буфер обміну. Щоб замінити первинні значення модифікованими, потрібно виділити вихідні комірки з текстом, в меню "Правка" обрати команду "Специальная вставка", встановити перемикач "Значения" і натиснути кнопку ОК. Після цього можна видалити тимчасові формули.

Функції «ЕТЕКСТ» і «ЕНЕТЕКСТ»:

Функції «ЕТЕКСТ» (ISTEXT) і «ЕНЕТЕКСТ» (ISNOTEXT) перевіряють, чи є значення текстовим. Синтаксис:

=ЕТЕКСТ(значення)

=ЕНЕТЕКСТ(значення)

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

=ЕТЕКСТ(А1)

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

=ЕНЕТЕКСТ(А1)

Excel повертає логічне значення БРЕХНЯ.

Розглянемо на прикладі принцип роботи текстових функцій «НАЙТИ» і «ПОИСК»:

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

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

Для виділення імені необхідно за допомогою функції «НАЙТИ» визначити порядковий номер першого пропуску в тексті, потім номер другого пропуску і виділити підрядок між пропусками.

Для обробки тексту в комірці А2 формула виглядатиме так:

=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-НАЙТИ(" ";A2;1)-1)

Не лякайтеся. Функція «НАЙТИ» використана тут 4 рази для обчислення місцеположення потрібного підрядка. Перший пропуск в комірці А2 має порядковий номер НАЙТИ(" ";A2;1). Отже ім'я починається з символу номер НАЙТИ(" ";A2;1)+1.

Другий пропуск йде відразу за ім'ям. Щоб знайти його, потрібно шукати пропуск не з початку рядка, а починаючи з символом, наступним за першим пропуском. Виходить конструкція НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1). Якщо з отриманого числа відняти місце першого символу імені, ми отримаємо довжину імені.

ФУНКЦІЇ ДАТИ І ЧАСУ

Представлення дати і часу мають одну особливість. При введенні дати або часу користувач вводе послідовність символів, яка не є числом, але з цими символами можна виконувати обчислення: порівнювати, додавати, віднімати і т. д. Тому в Excel, наряду з текстовим представленням дати і часу, існує і числове представлення.

За точку відліку за замовчанням прийнята дата «1 января 1900 года», а верхньою границею є «31 декабря 2078 года». Спосіб обчислення у випадку необхідності можна вказати у списку параметрів. Вибір способу обчислення визначається вибором стандарту, який використовується, кількістю днів у році і тривалістю місяця.

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

Для роботи з функціями дати і часу в Excel використовується «Мастер функций», який можна викликати за допомогою команди Головного меню - Вставка \ Функция…, або за допомогою кнопки «Вставка функции» яка розташована поряд з Рядком формул вікна MS Excel. Після виклику вікна «Мастер функций» потрібно у ньому ж обрати категорію «Дата и время» (див. мал. 2) після чого у відповідному полі буде відображено всі функції дати і часу Excel, значення яких можна дізнатися за допомогою гіперпосилання «Справка по этой функции», що знаходиться у цьому ж вікні, тим самим викликавши вікно довідки MS Excel.

Функція «ВРЕМЗНАЧ» перетворює час із текстового формату в числовий, а «ВРЕМЯ» - навпаки, за заданими годинами, хвилинами і секундами повертає час у числовому форматі.

Функція «МИНУТЫ» з числового або текстового формату дати і часу повертає лише хвилини. Якщо ввести одночасно і дату і час у формулу «=МИНУТЫ("10.07.95 18:30:15")», то повернуто буде лише число хвилин (для даної формули – «30»). Аналогічно працюють функції «СЕКУНДЫ» і «ЧАС» - вони повертають для введеного значення лише секунди і години відповідно.

Ф

Мал. 2

ункція «ГОД» повертає рік аргументу. Аналогічно працюють функції «ДЕНЬ» і «МЕСЯЦ». Функція «ДАТА» потребує введення року, місяця і дня та повертає дату у числовому форматі, функція «ДАТАЗНАЧ» просто перетворює дату з текстового формату у числовий (див. табл. 2).

Таблиця 1

Функції часу і результати їх роботи

Функції часу

Формати представлення

Загальний («Общий»)

Ч:ММ:СС

=ВРЕМЯЗНАЧ(18:30:15)

0,7710069

немає

=ВРЕМЯ(18;30;15)

6:30 РМ

18:30:15

=МИНУТЫ(0,771007)

30

немає

=МИНУТЫ(18:30:15)

30

немає

=СЕКУНДЫ(С3)

15

немає

=СЕКУНДЫ(0,771007)

15

немає

=ЧАС(В3)

18

немає

=ЧАС(0,771007)

18

немає

Таблиця 2

Функції дати і результати їх роботи

Функції дати

Формати представлення

Загальний («Общий»)

Д ММММ ГГГГ

=ГОД(9.07.95)

1995

немає

=ГОД(В5)

1995

немає

=ДАТА(95;7;9)

9.07.95

9 июля 1995

=ДАТАЗНАЧ(9.07.95)

34889

9 июля 1995

=ДЕНЬ(D5-9)

30

немає

=ДЕНЬНЕД(9.07.95)

1

немає

=МЕСЯЦ(D5)

7

немає

=ТДАТА()

9.07.95 18:48

9 июля 1995

Функція «ДЕНЬНЕД» повертає номер дня тижня. При цьому може бути обрано спосіб нумерації. За замовчанням неділя («воскресенье») вважається першим днем тижня.

Функція «ТДАТА» повертає поточну дату і час у числовому форматі.

V. Закріплення нового матеріалу.

VІ. Підведення підсумку уроку.

VІІ. Завдання до наступного уроку: конспект.

Викладач: Теницький С. М.

5

Зверніть увагу, свідоцтва знаходяться в Вашому особистому кабінеті в розділі «Досягнення»

Курс:«Розроблення і використання цифрового освітнього контенту в освітньому процесі Нової української школи »
Ілляхова Марина Володимирівна
30 годин
590 грн
590 грн

Всеосвіта є суб’єктом підвищення кваліфікації.

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

Співпраця із закладами освіти.

Дізнатись більше про сертифікати.