Основні функції – ЧАСТИНА 2

Функція ROUND (ROUND) вкорочує число-аргумент до зазначеної кількості десяткових знаків Округлення виконується за стандартними правилами: цифри менше 5 просто прибираються, а замість цифр більше або рівних 5 додається 1 у попередній розряд Функція записується так:

= ROUND (число колічество_ціфр)

Аргументколічество_ціфрможе бути і позитивним, і негативним цілим числом У першому варіанті округлення проводиться як звичайно: від меншого розряду до більшого, до заданої кількості праворуч від десяткової точки Якщо даний аргумент негативний, число округлюється на вказану кількість розрядів ліворуч від десяткової точки Коли значення цього аргументу дорівнює 0, всі число замінюється на найближче ціле Наприклад, формула = ROUND (123,45 б7 -2) повертає значення 100, а формула

= ROUND (123,4567 3)-значення 123,457 відповідно

Функції ОКРУГЛВВЕРХ (ROUNDUP) і ОКРУГЛВНИЗ (R0UNDD0WN) мають той же синтаксис, що і функція ОКРУГЛ, але, що випливає з їхніх назв, завжди діють в напрямку або «вгору» – збільшуючи на одиницю старший розряд, або «вниз» – зазначена кількість десяткових цифр обрізається

Функції парних і непарних

Для округлення чисел також підходять функція парна (EVEN) і непарні (ODD) Перша округлює значення до найближчого більшого (за модулем) парним числом, а друга – до найближчого більшого (за модулем) непарного числа Негативні величини, відповідно, округлюються НЕ вверх, а вниз Наприклад, формула = парні (23,4) повертає значення 24, а формула = непарні (-4) – значення -5

Функції ОКРВНІЗ і ОКРВВЕРХ

Це ще дві функції даної категорії Перша – ОКРВНІЗ (FLOOR) – округлює число в меншу сторону до найближчого кратного для заданого множника, а функція ОКРВВЕРХ (CEILING) – виправдовуючи свою імя, в більшу Синтаксис обох функцій однаковий вони мають аргументи число і множник І звичайно, приклад: формула = ОКРВНІЗ (23,4 0,5) повертає значення 23, а формула = ОКРВВЕРХ (5 1,5)

– число 6

Функція ОКРУГЛТ

Припустимо, потрібно округлити числа так, щоб вони стали кратні не 10, а, наприклад, 16, а потім представити їх у вигляді часток, які на практиці, як правило, не бувають більше шістнадцяткових Функція ОКРУГЛТ (MRОUND), що входить до складу надбудови Пакет аналізу (Analysis ToolPak), здатна округлити будь-яке число до заданого множника (Приєднання до Excel Пакета аналізу детально розглянуто в розділі «Установка пакета аналізу» глави 13) Її синтаксис наступний:

= ОКРУГЛТ (число точність)

Так, за формулою = ОКРУГЛТ (А1 0,0625) число, що міститься в комірці А1, буде округлене до однієї шістнадцятої Округлення виконується в більшу сторону, якщо залишок від ділення числа на точність більше або дорівнює половині

точностіЯкщо потрібно перетворити таким чином результат, одержуваний за іншою формулою, поставте останню замість аргументу число *

Функція ЦІЛЕ і ОТБР

Функція ЦІЛЕ (INT) обрізає число в меншу сторону до найближчого цілого

Наприклад, обидві формули:

= ЦІЛЕ (100,01)

= ЦЕЛ0Е (100,99999999)

повертають значення 100, хоча дріб 100,99999999 практично дорівнює числу 101 Якщо аргумент функції менше нуля, то воно також буде скорочуватися до найближчого цілого ліворуч, тобто як якби аргументами в наведених вище прикладах були ті ж числа, але зі знаком мінус, функція ЦІЛЕ повернула б значення -101

Функція ОТБР (TRUNC) відкидає всі цифри праворуч від десяткової коми,

незалежно від знака числа Вона має синтаксис:

= ОТБР (число колічество_ціфр)

1 Нерідко виникає необхідність представити число з відомою для нього точністю, наприклад в результатах наукового експерименту На жаль, Excel не підтримує таку можливість У функції ОКРУГЛТ під точністю розуміється якесь абсолютне кратне значення Тому насмілюся запропонувати вашій увазі адаптовану під VBA функцію свого колеги, математика за родом діяльності, з реалізованої їм статистичної бібліотеки Вона дозволяє форматувати (з округленням) дані відповідно до досягнутої для них точністю (не гірше А / 3 (%), для довірчої ймовірності 70%) Наприклад, запис = Rnd (Al 0,5) представляє число 123,456789 в комірці А1 з точністю 0,5% – 123,46 Функція ж ОКРУГЛТ (123,456789 0,62) видасть результат 123,38, тобто кратний 0,62 (а не з 0,5% точності) Аргументи функції Rnd: X – Число (у тому числі в експоненційної формі, представлений варіант для спрощення не працює для діапазону), А – точність (0-100), а текст її наведено нижче:

Function Rnd$(X, A)

If X = 0 Then Rnd = &quot 0&quot : Exit Function AX = 150

For IA = 0 To 5 Кількість точних цифр без 1

If A> АХ Then Exit For AX = AX * 01

Next

AX = Abs(X): IE = IA Int(ApplicationLogl0(AX))

R $ = Str $ (Int (AX * 10 л IE + 05) * 10 л (-IE)): If X < 0 Then Mid $ (R $, 1, 1) = "-" IE = InStr (R $, "E")

If IE &gt 0 Then Mid$(R$, IE, 1) = &quote&quot: R$ = Left$(R$, IE + 1) + FnAbs$(Val(Right$ / (RS 3))) If IE = 0 And Len(R$) &gt 10 Then -&gt Exp Form

P$ = Mid$(R$, 3, IA): If Val(P$) &gt 0 Then P$ = &quot.&quot + P$ Else P$ = &quot&quot R$ = Left$(R$, 2) + P$ + &quote+&quot + FnAbs$(Len(R$) 2)

End If

If InStr(R$ &quot.&quot) = 2 Then R$ = Left$(R$, 1) + &quot0&quot + Mid$(R$, 2) 0..1

I = InStr (R $, .”): If I 0 Then Mid $ (R $, I) = , Системний роздільник

Rnd = R$

End Function

Function FnAbs$(Value%)

If Value% &gt= 0 Then FnAbsS = Mid$(Str$(Value%) 2) Else FnAbs$ = Str$(Value%) End Function

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

= ОТБР (13,978) повертає значення 13, а формула = ОТБР (13,978 1) – число 13,9

Текстові функції

Текстові функції в Excel замінюють Більшість найбільш корисних інструментів з обробки тексту, які можна зустріти в звичайних текстових редакторах Наприклад, такі функції, як СЖПРОБЕЛИ (TRIM) і ПЕЧСІМВ (CLEAN), видаляють з тексту зайві прогалини і недруковані символи – це дуже важливо при чищенні імпортованих текстових файлів і досить складно виконати за допомогою звичайних операцій пошуку і заміни Функції ПРОПІСН (UPPER), СТРОЧН (LOWER) і ПРОПНАЧ (PROPER) впливають на регістр букв, переводячи малі літери в прописні або, навпаки, замінюючи на заголовну кожну першу букву слова

Вбудовані текстові функції дозволяють перетворювати в текст Excel документи, створені в інших додатках, включаючи формули Це можливо завдяки команді Спеціальна вставка (Paste Special) меню Виправлення

Тут ми зупинимося лише на деяких, максимально корисних представниках даної категорії Інформацію про всі текстових функціях ви знайдете в додатку В,

«Вбудовані функції Excel»

Найбільш корисні текстові функції

Розглянуті нижче функції перетворять числові текстові значення в числа, а числа – в рядки символів, а також виконують різні операції над рядками символів

Функція ТЕКСТ

Функція ТЕКСТ (TEXT) являє число текстової рядком заданого формату Вона записується так:

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

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

= ТЕКСТ (98/4 0,00) ми отримаємо текстову рядок 24,50 Для завдання необхідного формату використовуються стандартні символи Excel ($, #, 0, прогалини і т д), крім

символу зірочки (*) також не допускається застосування формату Загальний (General) –

ДИВИСЬ ТАКОЖ

Детальніше про символи форматування див розділ 8, табл 81, «Символи, які використовуються для кодування форматів», і табл 82, «Вбудовані коди форматів»

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

Подібно функції ТЕКСТ, функція РУБЛЬ (DOLLAR) також перетворює число в текст, але повертає його завжди в грошовому форматі з заданою кількістю десяткових знаків Функція має синтаксис

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

Аргументи визначають число або посилання на клітинку, яка містить числове значення, і кількість знаків після коми Відповідно, формула = РУБЛЬ (45,899 2) повертає текстовий рядок 45,90 р Зверніть увагу, що програма при необхідності округлює результуюче значення

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

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

Функція ДЛСТР (LEN) повертає кількість символів у текстовому рядку Аргументом цієї функції може бути число, рядок символів, укладена в лапки, а також посилання на клітинку Незначущі нулі ігноруються Таким чином, за формулою

= ДЛСТР (Тест) вийде значення 4

Функція ДЛСТР видає число символів відображається, а не збереженої в комірці рядка Нехай, наприклад, в комірці А10 міститься формула = А1 + А2 + А3 + А4 + А5 + + А6 + А7 + А8, результат виконання якої дорівнює 25 У цьому випадку формула = ДЛСТР (А10) поверне значення 2, тобто кількість символів у рядку 25 Осередок, на яку посилається аргумент функції ДЛСТР, може містити й інші текстові функції Так, якщо в комірці А1 зберігається формула = П0ВТОР (- *; 75), за якою 75 разів повторюється група з двох символів – дефіса і зірочки, формула = ДЛСТР (А1) поверне значення 150

Функції СИМВОЛ і КОДСІМВ

У будь-якому компютері символи представляються за допомогою числових кодів Найбільш поширеною системою кодування є ASCII (American Standard Code for Information Interchange) У цій системі всі цифри, букви і інші символи записуються числами від 0 до 127 (з урахуванням національних алфавітів – до 255)

Функції СИМВОЛ (CHAR) і КОДСІМВ (CODE) якраз і мають справу з кодами ASCII Функція СИМВОЛ повертає символ, асоційований з заданим числом, а функція КОДСІМВ, навпаки, повертає код ASCII для першого символу її аргументу Таким чином, за формулою = СІМВ0Л (83) вийде буква S, а формула = KОДCІMB (S) видасть число 83 Якщо в якості аргументу функції КОДСІМВ використовується текст, обовязково його у лапки в іншому випадку в комірці зявиться помилкове значення # ІМЯ.

РАДА

Якщо ви часто вставляєте в документи нестандартні, відсутні на клавіатурі символи, то замість виклику команди Вставка ► Символ (Insert ► Symbol) і роботи з діалоговим вікном зручніше скористатися функцією СИМВОЛ Наприклад, щоб створити знак торгової марки ®, потрібно ввести формулу = СИМВОЛ (174)

Функції СЖПРОБЕЛИ і ПЕЧСІМВ

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

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

Функція збігаючись

Функція збігаючись (EXACT) порівнює два рядки на предмет повного збігу, включаючи і регістр букв Ігнорується тільки різне форматування Якщо обидва рядки ідентичні, функція повертає значення ІСТИНА, в іншому випадку – значення БРЕХНЯ Обидва аргументи повинні бути ланцюжками символів, укладених в лапки, або ж посиланнями на комірки, в яких зберігається текст Наприклад, якщо комірки А5 і А6 містять один і той же текст Разом, то формула = С0ВПАД (А5 Аб) поверне значення ІСТИНА

ДИВИСЬ ТАКОЖ

Детальніше порівняння рядків символів розглядається в розділі «Умовні вирази»

глави 12

Функції перетворення регістра букв

Для управління регістрами символів в текстових даних в Excel передбачені три функції Перші дві ПРОПІСН (UPPER) і СТРОЧН (LOWER) перетворять всі букви рядка в прописні (заголовні) або рядкові відповідно Функція ПРОПНАЧ (PROPER) робить великою першу букву кожного слова, а інші літери слів замінює на рядкові Нехай, наприклад, в комірці А1 міститься текст Сергій ІВАНОВ Тоді формула = ПР0ПІСН (А1) поверне значення СЕРГІЙ ІВАНОВ, формула

= СТРОЧН (А1) – значення Сергій иванов а формула = ПР0ПНАЧ (А1) – Сергій Іванов

Варто врахувати, що якщо в тексті зустрічаються знаки пунктуації, дефіси і т д, за допомогою цих функцій можна отримати несподіваний результат Так, якщо в комірці А1 зберігається текст чорно-біла фотографія, після обробки його функцією ПРОПНАЧ ви побачите рядок Чорно-Біла Фотографія

Функції для роботи з підрядками

Функції ШУКАТИ (FIND), ПОШУК (SEARCH), ПРАВСИМВ (RIGHT), ЛЕВСИМВ (LEFT), ПСТР (MID), ПІДСТАВИТИ (SUBSTITUTE), ЗАМІНИТИ (REPLACE) і СЦЕПИТЬ (CONCATENATE) знаходять і повертають фрагменти текстових рядків або використовуються для обєднання декількох рядків символів в одну

Функції ШУКАТИ та ПОШУК

Функції ШУКАТИ (FIND) і ПОШУК (SEARCH) призначені для визначення положення текстового фрагмента в рядку Обидві функції повертають номер символу, з якого починається рядок-зразок (Прогалини і знаки пунктуації розглядаються Excel як символи) Обидві функції працюють практично однаково, за винятком того, що при пошуку за допомогою функції ШУКАТИ враховується регістр букв, а функція ПОШУК допускає застосування групових символів Синтаксис в обох випадках однаковий:

=НАЙТИ(искомый_текстпросматриваемый_текстнач_позиция) –

ПОШУК (іскомий_текст проглядається   текст нач   позиція)

Аргумент іскомий_текст задає послідовність символів, яку треба знайти, а аргумент просматріваемийjnencm визначає власне текст, в якому проводиться пошук Необовязковий аргументіач_позіція задає позицію в тексті, символи лівіше якої з розгляду виключаються Він використовується тоді, колиіскомий_текст зустрічається впросматріваемом_текстекілька разів Якщо опустити останній аргумент, Excel поверне позицію першого входження шуканого _текста Наприклад, щоб визначити порядковий номер символу «п» у рядкуЛогічні оператори, введіть формулу = ШУКАТИ (п; Логічні оператори) За неї повернеться значення 13, як і слід було очікувати

Коли точна послідовність символів шуканого тексту невідома, краще використовувати функцію ПОШУК, яка розуміє групові символи: знак питання () І зірочку (*) Знак питання замінює собою один довільний символ, а зірочка відповідає будь-якій послідовності символів у зазначеній позиції рядка-зразка Припустимо, що імя Петро на робочому листі фігурує в різному написанні Щоб визначити, чи є це імя в осередку А1, введіть формулу

= ПОШУК (П Тр; А1) Якщо осередок А1 містить текст Романов Петро або Романов Петро, то за заданою формулою повернеться число 9, тобто початкова позиція шуканої рядка П Тр

Якщо вам заздалегідь невідомі довжина і точний склад шуканого тексту, застосовуйте символ шаблону * Так, щоб дізнатися положення слова Введення або Висновок в комірці А1, напишіть формулу = ПОШУК (В * од; А1)

Функції ПРАВСИМВ і ЛЕВСИМВ

Функція ПРАВСИМВ (RIGHT) повертає крайні справа символи в рядку, а функція ЛЕВСИМВ (LEFT) – все символи, відлічувані від початку рядка Обидві функції приймають однакові аргументи:текстікількість_символів,де аргумент кількість_символіввказує довжину фрагмента, обираного з правого або лівого кінця аргументу текст відповідно

Ці функції сприймають прогалини як символи, тому якщотекстпочинається з прогалин чи закінчується ними, у ряді випадків має сенс у аргументітекст використовувати функцію СЖПРОБЕЛИ (TRIM) Наприклад, нехай у комірці А1 зберігається текст Це текст, тоді формула = ПРАВСИМВ (А1 5) поверне слово текст

Функція ПСТР

Функція ПСТР (MID) використовується для вилучення заданого числа символів з рядка тексту з вказаною позиції:

= ПСТР (текст нач_позіція кількість_символів)

Виходячи з синтаксису функції, видно, що якщо в комірку А1 помістити рядокЦе просто дуже довге текстове вираження,на виході формули = ПСТР (А1 12 13) вийде значення дуже довге

Функції ЗАМІНИТИ і ПІДСТАВИТИ

Ці дві функції замінюють частину символів у тексті на нові Функція ЗАМІНИТИ (REPLACE) працює відразу з чотирма аргументами:старий_текст нач_позі-ція кількість_символів новий_текст,завдяки чому здатна на багато чудеса Припустимо, в комірці А1 міститься деякий «Вміст комірки»і ви хочете замінити його на«Наповнення осередку»Для цього введіть формулу

= ЗАМІНИТИ (А1 1 10 наповнення)

У функції ПІДСТАВИТИ (SUBSTITUTE) початкова позиція і число замінних символів не задаються, замість них зазначається конкретний текст Вона має наступний синтаксис:

=ПОДСТАВИТЬ(текстстарый_текстновый_текстномер_вхождения)

Припустимо, що в комірці А1 зберігається, скажімо,стіл,який потрібно помістити в осередок А2, трансформувавши при цьому в стілецьЗ цим завданням впорається введена в комірку А2 формула = ПІДСТАВИТИ (А1 о; у)

Аргумент номер_вхожденія можна опускати Він наказує Excel замінювати новим текстом тільки заданий входження рядкастарий_текстЙого корисність дуже відносна Наприклад, якщо комірка А1 містить Велике число нулів і ви хочете виправити словонулів на нулів, введіть формулу = ПІДСТАВИТИ (А1 о; у; 4) При опущеному аргументі номер_вхожденія програма підставила б замість всіх букв «о» в заданій рядку букву «у» в даному ж випадку замінюється лише четверта по порядку буква «о» Чи не простіше було зробити це за допомогою клавіатури

РАДА

За допомогою функції ПІДСТАВИТИ можна створити формулу обробки масивів, яка підраховує число входжень заданого рядка в дані, що зберігаються в певному діапазоні осередків:

=СУММ(ДЛСТР(&ltдиапазон&gt)-ДЛСТР(П0ДСТАВИТЬ(&ltдиапазон&gt Текст; “))) /

ДПСТР (текст)

За цією формулою обчислюється кількість входжень аргументу «текст» в <діапазон> Введіть її, дотримуючись правил створення формул масивів, описаним у розділі 12, і натисніть клавіші Ctrl + Shift + Enter

Функція СЦЕПИТЬ

Функція СЦЕПИТЬ (CONCATENATE) є еквівалентом текстового оператора & і використовується для конкатенації текстових рядків Її синтаксис наступний:

= СЦЕПИТЬ (текст1 текст2 ..)

Як аргументи виступають як текст, так і посилання на комірки, і їх число може досягати 30 Так, якщо в комірці В4 міститься текст1-й квартал,формула

= СЦЕПИТЬ (В4 ​​Всього за) повертає рядок Всього за 1-й квартал

ВИРІШЕННЯ ПРОБЛЕМ-—— –

Результат обєднання значень осередків, відформатованих як дати, чреватий несподіванками Справа в тому, що відображається на аркуші дата зберігається в комірці як звичайне число, і лише за рахунок форматування воно постає у звичному для нас вигляді Щоб уникнути складнощів, ще до застосування функції СЦЕПИТЬ перетворіть вміст комірок в текстову рядок за допомогою функції ТЕКСТ Нехай, наприклад, в комірці А1 знаходиться текст «Сегодня», а в комірку А2, отформатированную для відображення дати у вигляді ДЦММГГГГ, введена формула = ТДАТУ () Якщо просто застосувати формулу

= СЦЕПИТЬ (А1 “; А2), вийде щось на зразок Сьогодні 38050 (залежно від поточної дати) Щоб отримати правильний результат, потрібно використовувати формулу

= СЦЕПИТЬ (А1 “; ТЕКСТ (А2 ДДММГГТГ)) Зверніть увагу, що між двома сце-

пляемимі текстовими рядками вводиться символ пробілу у вигляді додаткового аргументу (“)

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*