Основные функции – ЧАСТЬ 2

Функция  ОКРУГЛ  (ROUND)  укорачивает  число-аргумент  до  указанного  количества десятичных знаков. Округление выполняется по стандартным правилам: цифры меньше 5 просто   убираются,   а   вместо    цифр   больше   либо   равных   5   добавляется   1   в предшествующий разряд. Функция записывается так:

=ОКРУГЛ(число;количество_цифр)

Аргумент  количество_цифр  может  быть  и  положительным,  и  отрицательным  целым числом. В первом варианте округление производится как обычно: от меньшего разряда к большему, до заданного количества справа от десятичной точки. Если данный аргумент отрицательный, число округляется на указанное количество разрядов слева от десятичной точки. Когда значение этого аргумента равно 0, все число заменяется на ближайшее целое. Например,   формула   =ОКРУГЛ(123,45б7;-2)  возвращает   значение   100,   а   формула

=ОКРУГЛ(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 = " 0" : Exit Function AX = 150

For IA = 0 To 5 ‘ Число точных цифр без 1

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

Next

AX = Abs(X): IE = IA Int(Application.Logl0(AX))

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

If IE > 0 Then Mid$(R$, IE, 1) = "e": R$ = Left$(R$, IE + 1) + FnAbs$(Val(Right$ / (RS. 3))) If IE = 0 And Len(R$) > 10 Then ‘-> Exp Form

P$ = Mid$(R$, 3, IA): If Val(P$) > 0 Then P$ = "." + P$ Else P$ = "" R$ = Left$(R$, 2) + P$ + "e+" + FnAbs$(Len(R$) 2)

End If

If InStr(R$. ".") = 2 Then R$ = Left$(R$, 1) + "0" + Mid$(R$, 2) ‘ 0…1

I = InStr(R$, "."): If I 0 Then Mid$(R$, I) = "," ‘ Системный разделитель

Rnd = R$

End Function

Function FnAbs$(Value%)

If Value% >= 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, табл. 8.1,  «Символы,  используемые для кодировки форматов», и табл. 8.2, «Встроенные коды форматов».

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

Подобно функции ТЕКСТ, функция РУБЛЬ (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). При опущенном аргументе номер_вхождения программа подставила бы вместо всех букв «о» в заданной строке букву «у»; в данном же случае заменяется только четвертая по порядку буква «о». Не проще ли было сделать это с клавиатуры?

СОВЕТ

С помощью функции ПОДСТАВИТЬ можно создать формулу обработки массивов, которая подсчитывает число вхождений заданной строки в данные, хранящиеся в определенном диапазоне ячеек:

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

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

По этой формуле вычисляется количество вхождений аргумента «текст» в <диапазон>. Введите ее, следуя правилам создания формул массивов, описанным в главе 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 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий