Уокенбах Формулы в Excel

Извлечение всех слов строки кроме первого

Следующая формула полностью возвращает содержимое ячейки А1,за исключением первого слова:

=ПРАВСИМВ(А1;ДЛСТР(А1)-НАЙТИ(" ";А1;1))

Например, если ячейка А1 содержит текст 2002 Текущий Бюджет, формула возвращает текст Текущий Бюджет.

Извлечение имени, отчества ифамилии

Предположим, что в одном из столбцов рабочего листа содержится именованный список сотрудников фирмы, состоящий из имен, отчеств и фамилий. Что сделать для того, чтобы разделить весь этот перечень на три отдельных столбца: один с именами, другой с отчествами и третий с фамилиями людей? Этазадача более сложна, чем может показаться напервый взгляд, поскольку отчество имеется нево всех строках. Темнеменее, онавсе-таки решаема.

Задача становится чуть более сложной, если имена, присутствующие в списке, также содержат титулы и звания (такие как, например, Mr. или Dr.) или сопровождаются некоторыми дополнительными подробностями (например, мл. или III).Фактически, приведенные ниже формулы не способны обработать такие сложные случаи. Тем не менее, они позволяют существенно продвинуться в решении этой задачи. Пользователю остается внести лишь несколько корректировок, чтобы вручную обработать частные случаи.

В примере, взятом для следующих ниже формул, предполагается, что список имен содержится в ячейке А1. Формула, возвращающая имя человека, выглядит весьма просто:

И, наконец, следующая формула возвращает фамилию:

= П Р А В С И М В ( А 1 ; Д Л С Т Р ( А 1 ) - Н А Й Т И ( " * " ; П О Д С Т А В И Т Ь ( А 1 ; " " ; - * " ; Д Л С Т Р ( А 1 ) - Д Л С Т Р ( П О Д С Т А В И Т Ь ( А 1 ; " " ; " " ) ) ) ) )

Однако существует формула, которая позволяет более простым способом извлечь из списка отчества людей. Следующая формула может быть весьма полезна, если изначально использовать две другие формулы для получения имени и фамилии. Для этого необходимо поместить имячеловека в ячейку В1, а фамилию в ячейку D1.

=ЕСЛИ(ДЛСТР(В1&Б1)+2>=ДЛСТР(А1);" ";ПСТР(А1;ДЛСТР(В1)+2;ДЛСТР(А1)- ДЛСТР(В1&Б1)-2)

Как видно изрис. 5.6, этиформулы работают достаточно хорошо. Тем не менее, несколько проблем все же существует, особенно с именами, которые содержат четыре "слова".Но, как было сказано ранее, этоможет быть исправлено вручную.

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

Удаление изимен титулов изваний

Приведенная здесь формула позволяет отбросить от имени человека три часто употребляемых обращения: Mr., Ms. и Mrs. Например, если ячейка А1 содержит текст Mr. Fred Munster,следующая формула возвращает Fred Munster.

=ЕСЛИ(ИЛИ(ЛЕВСИМВ (А1 ; 2 ) = "Mr" ; ЛЕВСИМВ ( А 1 ; 3 ) = "Mrs" ; ЛЕВСИМВ ( А 1 ; 2) = "Ms" ) ; ПРАВСИМВ(А1;ДЛСТР(А1)-НАЙТИ(" " ; А 1 ) ) ; А 1 )

Подсчет количества слов вячейке

Следующая формула возвращает количество слов в ячейке А1:

=ДЛСТР(СЖПРОБЕЛЫ(А1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(А1);" ";""))+ 1

В этой формуле используется функция СЖПРОБЕЛЫ, которая удаляет лишние пробелы. После этого с помощью функции ПОДСТАВИТЬ в памяти создается новая строка без пробелов. Затем длина созданной строки вычитается из длины исходной строки, в результате чего определяется количество пробелов. Это значение увеличивается на 1, и, таким образом,подсчитывается количество слов.

Разделение текстовых строк без использования формул

Во многих случаях пользователь может отказаться от применения формул для того, чтобы разобрать строки насоставляющие ихэлементы, ииспользовать команду Данные^Текст по столбцам. При выборе этой команды на экране отображается мастер текстов приложения Excel, который содержит ряд диалоговых окон, позволяющих шаг за шагом преобразовать один столбец, содержащий данные, в несколько столбцов. Обратите внимание, чтоэффективней всего выбрать разделение данных по символам пробелов, установив переключатель С разделителями напервом шаге мастера ифлажок Пробел навтором шаге.

рдадвлители однин

-Обрами добор» д«*адх

Отиана 1 <Цюц. \[1щь>

Имейте в виду, что приведенная выше формула возвращает значение 1, если заданная ячейка не содержит данных. Решить эту проблему можно, усовершенствовав формулу следующим образом:

=ЕСЛИ(ДЛСТР(А1)=0;0;ДЛСТР(СЖПРОБЕЛЫ(А1))- ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(А1) ;" ";""))+1)

Создание собственных текстовых функций

спомощью языка VBA

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

Глава 25 этой книги содержит несколько дополнительных текстовых функций, написанных на VBA. Ниже мы просто кратко рассмотрим их описание.

1.Функция REVERSETEXT возвращает в заданную ячейку текст, располагая в нем символы в обратном порядке. Например, если в функцию как аргумент введен текст Evian, то возвращается текст naivE.

2.Функция ACRONYM возвращает первый символ каждого слова, используемого в качестве аргумента функции. Например, при использовании текста Power Utility Рак, функция возвращает PUP.

3.Функция SPELLDOLLARS возвращает число, введенное в качестве аргумента, прописью (как это часто встречается в чеках). Например, используя при вводе аргумент

123,45,функция возвращает текст Сто двадцать три и сорок пять сотых.

4.Функция SCRAMBLE возвращает случайный набор символов введенного аргумента. К примеру, введенный в качестве аргумента текст Microsoft,возвращается как oficMorts, либо в виде другой случайно полученной перестановки символов.

5.Функция ISLIKE возвращает значение ИСТИНА в том случае, если строка соответствует образцу, составленному из текста и групповых символов.

6.Функция CELLHASTEXT возвращает значение ИСТИНА, если ячейка, заданная как аргумент, содержит текст или значение в формате Текстовый. Эта функция позволяет преодолеть проблемы, которые мы рассматривали в начале этой главы.

7.Функция EXTRACTELEMENT извлекает элемент из строки, основываясь на определенном символе-разделителе (например,дефисе).

Резюме

Вэтой главе рассмотрены некоторые основные принципы управления в приложении Excel текстом, содержащимся в ячейках. Кроме того, здесь представлено много полезных примеров, в которых использованы текстовые функции.

Вследующей главе мы рассмотрим формулы, позволяющие вычислять даты, время и другие значения, связанные с временными периодами.

Главаб

Работа с данными даты и времени

Вэтой главе...

•Принципыуправления данными даты и времени в Excel

•Функции управления датами

•Функции управления временем

•Резюме

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

Примеры, которые будут использоваться в этой главе, соответствуют формату даты, принятому в русифицированном приложении Excel: месяц.день.год (или день/месяц/год). К примеру, формат даты 1.03.1952 означает 1 марта 1952года. Имейте в виду, что это не 3 января 1952года, как принято в форматеанглоязычного приложения. Формат даты англоязычного программногообеспечения может показаться пользователю не совсем логичным. Тем не менее, в американских параметрах настройкисистемы используетсяименноэтот формат.

Принципы управления данными даты ивремени в Excel

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

Информация, связанная с датой, приведена и в других главах этой книги. К примеру, в главе 7 приведены примеры расчета значений ячеек, использующих формат даты, а в главе 25 — некоторые полезные функции управления датами, написанные на VBA.

Понятие даты как порядкового номера

чает, что порядковый номер дня 1 соответствует 1 января 1900 года; порядковый номер 2 — 2 января 1900 года и так далее. Существование такой системы позволяет использовать формулы при работе с датами. Например, пользователь может создать формулу, которая будет вычислять количество дней между двумя датами.

Безусловно, понятие 0 января 1900 года может вызвать недоумение. Конечно, оно не является датой, которая соответствует нулевому дню, а, фактически, означает определенное время дня. Чуть позже в этой главе мы рассмотрим это понятие более подробно.

Для того чтобы представить числовое значение в виде даты, к ячейке, содержащей этот номер, необходимо применить формат Дата. Для этого вызовите диалоговое окно Формат ячеек и щелкните на вкладке Число, а затем выберите необходимый формат.

Excel 97 и более поздние версии этого приложения поддерживают даты в диапазоне от 1 января 1900 года до 31 декабря 9999 года (порядковый номер этой даты 2 958 465). Диапазон дат, доступный в более ранних версиях приложения, значительно меньше. Здесь используются даты, начиная с 1 января 1900 года и до 31 декабря 2078 года (порядковый номер 65 380).

Выберите систему дат: 1900или 1904

Фактически, Excel поддерживает две системы дат: система дат, начиная с 1900 года, и система с 1904 года. Какую из этих систем лучше всего использовать в рабочей книге, пользователь определяет самостоятельно, в зависимости от того, какая дата служит основанием для других дат. В системе дат, начинающейся с 1900 года день 1 января 1900 года имеет порядковый номер 1. В системе 1904 года дата отсчета —1 января 1904 года. По умолчанию для систем Windows приложение Excel использует систему дат, начинающую отсчет с 1 января 1900 года, а для систем Macintosh — дату, начиная с 1904 года. Тем не менее, и для систем Windows, и для обеспечения совместимости с файлами Macintosh, Excel также поддерживает систему дат 1904. Систему дат можно изменить, используя диалоговое окно Параметры. Чтобы вызвать его, выберите команду Сервис=>Параметры, а затем щелкните на вкладке Вычисления. Имейте в виду, что система дат не может изменяться, если на компьютере используется приложение Excel для Macintosh.

На самом деле, лучше всего использовать систему дат, установленную по умолчанию — начиная с 1900 года. Будьте особенно осторожны, если используете две различные системы дат в связанных рабочих книгах. К примеру, предположим, что Книга1 использует систему дат, начиная с 1904 года, и содержит в ячейке А1 дату 15/1/1999. Книга2, наоборот, использует систему дат с 1900 года, и содержит некоторую ссылку на ячейку А1 книги Книга1. В книге Книга2 эта дата будет отображаться как 14/1/1995. Таким образом, обе рабочие книги используют один и тот же порядковый номер дня (34 713), но в той и в другой книгах он будет интерпретироваться по разному.

Существует одно преимущество использования системы дат 1904. Эта система позволяет отображать отрицательные значения времени. Другими словами, при использовании системы дат 1900 решение такой задачи как 4:00 РМ - 5:30 РМ не может быть отображено. При использовании системы дат 1904, результат отображается как отрицательное время -1:30 (то есть разница составляет один час и 30 минут).

Введение даты

Конечно, пользователь может вводить дату как числовое значение, но, как правило, дата вводится с использованием любого из нескольких поддерживаемых в Excel форматов даты. После введения даты приложение Excel автоматически преобразует ее в соответствующий

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

Например, если пользователю необходимо ввести дату 1 июня 2002 года, то это можно сделать весьма просто — 1 Июнь, 2002, используя этот формат или любой другой из имеющихся форматов даты. Excel преобразует введенную информацию и сохраняет ее как числовое значение 37 408 — порядковый номер, соответствующий этой дате. После этого Excel применяет формат даты, заданный по умолчанию, поэтому содержимое ячейки может отображаться в представлении, несколько отличающемся от введенного пользователем.

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

Обратите внимание, что при активизации ячейки, содержащей дату, в строке формул отображается ее содержимое, представленное в заданном по умолчанию формате, который соответствует сокращенному виду даты, принятому в системе. Имейте в виду, что строка формул не отображает порядковый номер даты. Чтобы определить порядковый номер даты, просто перейдите от формата даты к числовому формату ячейки.

Чтобы изменить заданный по умолчанию формат даты, необходимо изменить региональные настройки самой системы. Для этого, находясь в Windows, откройте папку Панель управления, запустите аплет Язык и региональные стандарты, щелкните на кнопке Настройка, а затем перейдите на вкладкуДата. Краткий форматдаты,заданный по умолчанию в Excel, определяет опция, выбранная из раскрывающегося списка Краткий формат.Просто измените текущеепредставление на любоедругое.

Обратите внимание, в табл. 6.1 приведены образцы форматов даты, которые поддерживает Excel (использующиеся в параметрах настройки для России и Украины). Однако они могут отличаться, если на компьютере используются настройки другого региона.

; Таблица 6.1. Преобразование введенных дат вформаты, поддерживаемые Excel

Рассматривая примеры, приведенные в этой таблице, можно сделать вывод, что Excel обладает достаточно широкими интеллектуальными возможностями преобразования введенных дат. Однако и она не вполне совершенна. Например, она не признает в качестве даты следующую введенную информацию:

1.1 июня 2001

2.Июн-12001

3.Июн-1/2001

Эту информацию Excel понимает как текст. Поэтому, прежде чем использовать даты в формулах, убедитесь, что Excel поддерживает тот формат даты, который вы вводите. В противном случае, формулы, которые обращаются к этим датам, будут работать некорректно.

Существует еще один недостаток. Если введенная дата находится вне поддерживаемого диапазона дат, Excel также интерпретирует ее как текст. И, если порядковый номер, находящийся за пределами доступных для даты числовых значений, попытаться привести к формату даты, вместо значения будет отображаться ряд знаков "решетки" (#########).

Понятие времени как порядкового номера

Работа с числовыми значениями, представляющими собой характеристики времени — это не что иное, как простая детализация (то есть деление на десятичные доли) порядковых номеров, используемых в выражениях даты. Другими словами, при работе со временем приложение Excel использует разделение на дни. К примеру, если порядковый номер даты 1 июня 2001 года — это 37 043; полдень того же дня будет иметь значение 37 043,5.

Поиск даты

Иногда, если рабочий лист использует много дат, может возникнуть необходимость найти определенную дату. Для этого воспользуйтесь диалоговом окном Найти и заменить Excel, которое можно открыть, выбрав команду меню Правка^Найти или нажав комбинацию клавиш <Ctrl+F>. Имейте в виду, что Excel достаточно требователен к точности ввода искомой информации. Вам придется ввести полную четырехразрядную дату в поле Найти, находящемся в этом диалоговом окне. Формат даты должен соответствовать краткому представлению даты, принятому в системе. Это представление можно увидеть в строке формул.

Порядковый номер одной минуты эквивалентен приблизительно 0,00069444. Формула, приведенная ниже, вычисляет этот номер, умножая 24 часа на 60 минут, а затем разделив 1 на полученный результат. Таким образом, в знаменателе получается количество минут в одном дне (1 440).

= 1 / ( 2 4 * 6 0 )

Тем же способом в Excel определяется порядковый номер одной секунды — приблизительно 0,00001157. Для этого 1 делится на 24 часа, умноженные на 60 минут и 60 секунд. В этом случае, знаменатель содержит количество секунд в одном дне (86 400).

=1/(24*60*60)

Самая маленькая единица измерения времени в приложении Excel — одна тысячная доля секунды (миллисекунда). Порядковый номер приведенного ниже значения представляет время 23:59:59,999 или без одной тысячной секунды полночь:

0,99999999

В табл. 6.2 приведены различное время дня и соответствующие ему порядковые номера, по которым Excel распознает это время.

Таблица 6.2.Время дня ипреобразованиееговпорядковый...

Введение времени

Как и в случае с датами, пользователю нет необходимости вводить порядковый номервремени. Достаточно ввести в ячейку время в любом из форматов, поддерживаемых в Excel. Обратите внимание, в табл. 6.3 приведены некоторые примеры форматов времени, поддерживаемые в Excel.

Таблица 6.3. Преобразование введенноговремени вформат Excel

Поскольку приведенные выше примеры времени не связаны ни с каким определенным днем, Excel по умолчанию использует порядковый номер 0, соответствующий 0 января 1900 года, который, по своей сути, не является датой. Но в большинстве случаев у пользователя возникает необходимость объединить дату и время. Чтобы осуществить эту задачу, используйте совместный формат даты и времени, который состоит из формата даты, пробела и формата времени. Например, если в ячейку ввести текст 11:30 1