Применение Excel в экономике

8.5. Функции НАЙТИ и ПОИСК

Функции НАЙТИ и ПОИСК используются для определения позиции одной текстовой строки в другой. Обе функции возвращают номер символа,  с которого начинается первое вхождение искомой строки. (Excel рассматривает пробелы и знаки пунктуации как символы.) Эти две функции работают одинаково за исключением того,  что функция НАЙТИ учитывает регистр букв, а функция ПОИСК допускает использова­ние символов шаблона. Функции имеют следующий синтаксис: =НАЙТИ (искомый_текст;просматриваемый_текст;нач_позиция) =ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)

Аргумент искомый_текст задает текстовую строку, которую надо найти, а аргумент просматриваемый_текст — текст, в котором производится поиск. Любым из этих аргументов может быть строка символов, заключенная в двойные кавычки, или ссылка на ячейку. Необязательный аргумент нач_позиция задает позицию в просматриваемом тексте, с которой начинается поиск. Аргумент нач_позиция следует использовать в том случае, когда просматриваемый_текст содержит несколько вхождений искомого текста. Если вы опустите аргумент нач_позиция, Excel возвратит позицию первого вхождения. Эти функции возвращают ошибочное значение #ЗНАЧ!, когда искомый_текст не содержится в просматриваемом тексте, либо нач_позиция меньше или равна нулю, либо нач_позиция превышает количество символов в просматриваемом тексте, либо нач_позиция больше позиции последнего вхождения искомого текста.

Если точная последовательность символов искомого текста неизвестна, можно использовать функцию ПОИСК и включить в строку искомый_текст симво­лы шаблона: знак вопроса (?) и звездочку (*). Знак вопроса соответствует

одному произвольному символу, а звездочка заменяет любую последователь­ность символов в указанной позиции.

Предположим, что на рабочем листе имеется имя Петр в различном написании. Чтобы определить, есть ли это имя в ячейке А1, используйте формулу =ПОИСК("П?тр";А1)

Если ячейка А1 содержит текст Романов Петр или Романов Пётр, функция ПОИСК возвратит значение 9 — начальную позицию строки П?тр. Если вы не знаете, точное число символов, используйте символ шаблона *. Например, чтобы найти позицию строки Ввод или Вывод в тексте, находящемся в ячейке А1, используйте формулу =ПОИСК("В*од";А1)

4.6. Функции ЗАМЕНИТЬ и ПОДСТАВИТЬ

Эти   две   функции   заменяют   символы   в   тексте. Функция   ЗАМЕНИТЬ замещает часть текстовой строки другой текстовой строкой и имеет следующий синтаксис: =ЗАМЕНИТЬ(старый_текст; нач_позиция; количество_символов; новый_текст). Аргумент старый_текст — это текстовая строка, в которой вы хотите заменить символы. Следующие два аргумента, нач_позщия и количество_символов, задают символы, которые нужно заменить (относительно начала строки). Аргумент новый_текст задает вставляемую текстовую строку.

Предположим, что ячейка A3 содержит текст Первый квартал. Чтобы поместить этот же текст в ячейку А6, заменив первые шесть символов строкой Второй, выделите ячейку А6 и введите формулу =ЗАМЕНИТЬ(АЗ;1; 6; "Второй"). Эта формула возвращает новый текст Второй квартал. Заголовок в ячей­ке A3 останется неизмененным, и новый заголовок появится только в ячейке А6, в которую вы ввели эту формулу.

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

Предположим, что ячейка А4 содержит текст стол и вы хотите поместить его в ячейку D6, заменив при этом одну букву, чтобы получить слово стул. Используйте формулу =ПОДСТАВИТЬ (А4;" о",'"у")

Если вы введете эту формулу в ячейку D6, текст в ячейке А4 не изменится. Новый текст появится только в D6, то есть в ячейке, которая содержит эту формулу. Аргумент номер_вхождения является необязательным. Он предписывает Excel заменить только заданное вхождение строки старый_текст. Например, если ячейка А1 содержит текст Большое число нолей и вы хотите заменить нолей на нулей, введите формулу =ПОДСТА8ИТЬ(А1; "о"; "у";4). Число 4 в этой формуле указывает Excel, что на «у» нужно заменить только четвертое «о» в ячейке А1. Если аргумент номер_вхождения опущен, Excel заменяет все вхождения строки старый_текст на строку новый_текст.

8.7. Функция ПОВТОР

Функция ПОВТОР позволяет заполнить ячейку строкой символов, по­вторенной заданное число раз. Эта функция имеет следующий синтаксис:

=ПОВТОР( текст; число_повторений). Аргумент текст — это размножаемая строка символов, заключенная в кавычки. Аргумент  число_повторений указывает,  сколько  раз  нужно  повторить текст. Хотя число_повторений может быть любым положительным числом, длина возвращаемого значения функции ПОВТОР ограничена 255 символами. Если аргумент число_повторений равен 0, функция ПОВТОР оставляет ячей­ку пустой, а если он не является целым числом, эта функция отбрасывает десятичные знаки после запятой.

Предположим, что вы хотите создать строку из 150 символов звездочки. Введите формулу =ПОВТОР("*";150) В результате вы получите строку из 150 звездочек.

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

=ПОВТОР("-*";75)

Таким образом, аргумент число_повторений задает количество повторений аргумента текст, а не общее число создаваемых символов. Если строка текст содержит два символа, длина возвращаемой строки будет равна удво­енному значению аргумента число повторений.

4.8. Функция СЦЕПИТЬ

Функция СЦЕПИТЬ является эквивалентом текстового оператора & и используется для объединения строк. Эта функция имеет следующий синтаксис: =СЦЕПИТЬ( текст1;текст2;...) В функции можно использовать до 30 аргументов, объединяемых в одну строку. Например, если ячейка В4 содержит текст 1-й квартал, следующая формула возвращает текст Всего за 1-й квартал. =СЦЕПИТЬ("Всего за ";В4)

5. Логические функции

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

5.1. Логические выражения

Логические выражения используются для записи условий, в которых сравни­ваются числа, функции, формулы, текстовые или логические значения. На­пример, каждая из представленных ниже формул является логическим выра­жением:

=А1>А2;=5-3<5*2;=СРЗНАЧ(В1:В6);=СУММ(6; 7; 8);=С2="Среднее" =СЧЁТ(А1:А10);=СЧЁТ(В1:В10);=ДЛСТР(А1)=10

Любое логическое выражение должно содержать, по крайней мере, один опе­ратор сравнения, который определяет отношение между элементами логичес­кого выражения. Например, в логическом выражении А1>А2 оператор больше (>) сравнивает значения в ячейках А1 и А2. Следующая таблица содержит список операторов сравнения Excel.

Список операторов сравнения Microsoft Excel.                                                        

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0). Например, следующее логическое выражение возвращает значение ИСТИНА, если значение в ячейке Z1 равно 10, и ЛОЖЬ, если Z1 содержит любое другое значение: =Z1=10

5.2. Функция ЕСЛИ

Функция ЕСЛИ имеет следующий синтаксис:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Например, следующая формула возвращает число 5, если значение в ячей­ке А6 меньше 22: =ЕСЛИ(А6<22;5;10). В противном случае формула возвращает 10.

В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, следующая формула возвращает сумму значений в ячейках от А1 до А10, если эта сумма положительна: =ЕСЛИ(СУММ(А1:А10)>0;СУММ(А1:А10); 0). В противном случае формула возвращает 0.

В функции ЕСЛИ можно также использовать текстовые аргументы. Напри­мер, лист, представленный на рис.7, содержит результаты экзаменов для группы студентов. Следующая формула в ячейке G4 проверяет средний балл, содержащийся в ячейке F4: =ЕСЛИ(С4>75%;"Сдал";"Не сдал").

Если средний балл оказывается больше 75 %, функция возвращает текст Сдал; если же средний балл меньше или равен 75 %, функция возвращает текст Не сдал.

Рис.7. Функция ЕСЛИ возвращает текстовую строку

Вы можете использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0. Например, следующая формула возвращает пустую строку (""), если логическое выражение имеет значение ЛОЖЬ: =ЕСЛИ(СУММ(А1:А10)>0;СУММ(А1: А10);""). Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например, пусть имеется формула =ЕСЛИ(А1="Тест";100;200)

Эта формула возвращает значение 100, если ячейка А1 содержит строку Тест, и 200, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

5.3. Функции И, ИЛИ и НЕ

Три дополнительные функции — И, ИЛИ и НЕ - по­зволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения: =, >, <, >=, <= и <>. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют следующий синтаксис:

=И(логическое_значение1;логическое_значение2;... ;логическое_значениеЗО) =ИЛИ(логическое_значение1;логическое_значение2;... ;логическое_значениеЗО) Функция НЕ имеет только один аргумент и следующий синтаксис: =НЕ(логическое_значенне)

Аргументы функций И, ИЛИ и НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

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

=ЕСЛИ(И(С4<5;Р4>75);"Сдал";"Не сдал")

Рис.8.  Функция И позволяет создавать сложные логические выражения

Хотя функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Например, следующая формула возвращает текст Сдал, если средний балл больше 75 или если студент имеет меньше 5 пропус­ков занятий без уважительных причин: =ЕСЛИ(ИЛИ(С4<5;Р4>75%);"Сдал";"Не сдал"). Таким образом,  функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвра­щает логическое значение ИСТИНА, только если все логические выражения истинны.

Функция НЕ меняет значение своего аргумента на противоположное логиче­ское значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значе­ние ИСТИНА. Например, следующая формула возвращает текст Прошел, если значение в ячейке А1 не равно 2: =ЕСЛИ(НЕ(А1=2);"Прошел";"Не прошел")

5.4. Вложенные функции ЕСЛИ

Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ и НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Например, в следующей формуле используются три функции ЕСЛИ: =ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";

ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Увы!")))

Если значение в ячейке А1 является целым числом, формула читается следу­ющим образом: «Если значение в ячейке А1 равно 100, возвратить строку Всегда. В противном случае, если значение в ячейке А1 находится между 80 и 100 (точнее, от 80 до 99 включительно), возвратить строку Обычно. В про­тивном случае, если значение в ячейке А1 находится между 60 и 80 (от 60 до 79 включительно), возвратить строку Иногда. И наконец, если ни одно из этих условий не выполняется, возвратить строку Увы!».