Лабораторна робота 14

Використання функцій MS Excel

Функції TRUNC IF, SUMM, RIGHT, CONCATENATE

Запис суми словами

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

В комірку А1 записуємо число 1234567,89

В другий рядок, який займає діапазон А2:Н2 записуємо назву розрядів чисел (рис.1).

Рис. 1. Назва розрядів чисел

Далі поділимо число за розрядами. Для цього в комірку А3 запишемо формулу:

=TRUNC(А1/1000000;0)

У результаті ділення числа, введеного у комірку А1, на число розряду отримаємо кількість одиниць мільйонів, далі за допомогою математичної функції TRUNC (ОТБР), відкинемо дробову частину.

Решта формул, які знаходяться в третьому рядку подані на рис. 2 і 3.

Рядок 4, який слугує для визначення цифри, яка відповідає в заданому числі конкретному розряду.

Формула у комірці А5 для формування запису цифр класу мільйонів виглядає так:

=IF(А4=0;"";IF(А4=1;"один";А6))

Формула запитує: що знаходиться у комірці А4? Якщо у комірці А4 міститься значення «0», то запис не здійснюється. Якщо у комірці А4 — значення «1», то записується «один» (мільйон). Якщо комірка містить інше значення, то провадиться переадресація у комірку А6 тощо.

Рис.2. Формули для написання чисел від мільйонів до тисяч

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

У комірці G5 знаходиться формула:

=IF(G4=1;IF(F4=1;"одинадцять";"одна");G6)

Якщо у комірках G4 і F4 знаходиться значення «1», то функція IF повертає словами число «одинадцять». Якщо ж значення «1» знаходиться лише у комірці F4, а у комірці G4 — інше значення, то повертається слово «одна» (гривня). Якщо ж у комірці F4 знаходиться будь-яке інше значення, відмінне від «1», то формула переадресовує вирішення завдання у комірку G6.

Для відображення сум з копійками у комірці Н4 запишемо формулу:

=IF(НЗ<=9;0;"")

Ця формула потрібна тоді, коли кількість копійок менша або дорівнює значенню «9», то перед цифрою додається додаткове значення «0». Інакше при написанні сум, буде показано значення копійок лише з одним нулем замість двох, наприклад, «20 грн. 0 коп.».

Формування та відмінювання назви класів

У комірках А14 і D14, проводиться формування і відмінювання слів — назв класів. Цей блок ґрунтується на аналізі останньої букви цифри класу.

У комірці А14 знаходиться формула:

=IF(А4=0;"";IF(RIGHT(А5;1)="н";"мільйон";IF(RIGHT(А5;1)="а";"мільйони"; IF (RIGHT(А5;1)="и";"мільйони";''мільйонів"))))

Якщо у комірці А4 є значення «0», то запис не здійснюється. Якщо найменування числа закінчується на букву «а» (два), то формула повертає текст «мільйони»; на букву «и» (чотири) — «мільйони» тощо.

Для того щоб визначити, на яку букву закінчується назва останньої цифри числа, використовується текстова функція RIGHT.

Рис.4 Формула у комірці А14

У комірці D14 знаходиться формула:

= IF (В4+С4+D4=0;""; IF (RIGHT (D5;1)="а";"тисяча"; IF(RIGHT(D5;1)="і";"тисячі"; IF (RIGHT (D5;1)= "и";"тисячі";"тисяч''))))

Формула спочатку підсумовує значення, що знаходяться у комірках В4, С4, D4, і визначає, чи є взагалі розряди класу тисяч у цьому числі.

У комірці G14 знаходиться формула:

= IF(A4+B4+C4+D4+E4+F4+G4=0;"Нуль гривень"; IF(RIGHT(G5;1)= а";"гривня"; IF(RIGHT(G5;1)="і"; "гривні"; IF(RIGHT (G5;1)="и";"гривні";"гривень"))))

Перш ніж провідміняти слово, формула провадить опитування на наявність у цьому числі цілого значення гривень. Якщо немає цілого значення, то виводиться напис «Нуль гривень».

Рис.5. Формули у комірках А15 - А21 для поєднання усіх елементів написання суми словами

У комірці А15 знаходиться формула:

= CONCATENATE(A5;" ";A14;" ";B5;" ";C5;" ";D5;" ";D14;" ";E5;" "; F5;" ";G5;" ";G14;" ";H3;H4;" коп ")

яка об’єднує усі текстові значення елементів написання суми словами в єдине ціле. Наприклад, при формуванні словами числа 1234567,89:

=CONCATENATE ("один";" ";"мільйон";" ";"двісті";" ";"тридцять";

" "; "чотири";" ";"тисячі";" ";"п’ятсот";" ";"шістдесят"; " ";"сім";" ";"гривень";" ";89 коп)

Між посиланнями на адреси комірок у функції CONCATENATE треба обов'язково поставити пропуски. У цій формулі посилання зроблено на усі адреси комірок, які «пишуть» цифри та найменування розрядів.

Формула у комірці А16:

= TRIM (А15)

Текстова функція TRIM видаляє з тексту усі пропуски, за винятком одиночних пропусків між словами.

Формула у комірці А17:

=LEFT(А16;1)

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

Формула у комірці А18:

=UPPER(А17)

Функція UPPER робить букви у тексті великими. Ця функція необхідна для того, щоб написати великими буквами найлівішу букву у комірці А17.

У комірці А19 формула:

=LEN(А16)

Вона визначає кількість символів в отриманому текстовому рядку у комірці А16.

У комірці А20 формула:

=RIGHT(А16;А19-1)

виділяє усі праві символи в отриманому текстовому рядку, крім першої букви.

У комірці А21 формула:

=CONCATENATE (А18;А20)

Завдання цієї функції – об’єднати першу велику букву з рештою тексту.

На рис.6 показано вигляд таблиці після внесення у всі комірки необхідних формул.

Рис.6. Таблиця написання числа словами

Завдання:

В комірку А1 запишіть число 5863264,21.

Який запис з’явився у комірці А21?