Иванов А.А.; Петров В.С.; Кузин А.И.

Теперь при входе в клетку А2 рядом справа (рис. 3-5.8) от нее возникнет кнопка 6 вызова сформированного списка, из которого можно выб­рать нужную фамилию. Такой механизм не только гарантирует правильность и унификацию отображения фамилий, но и облегчит пользователю ввод данных. Он должен просто выбрать нужную фамилию из предлагаемого списка.

«««

Примечания могут стать очень полезным элементом оформления таблиц. Они не являются частью данных, но могут быть присоединены (“приклеены”) к любой ячейке для чего следует щелкнуть правой кнопкой мыши и в контекстном меню выбрать пункт Создать примечание (или воспользоваться главным меню Вставка+Примечание). Тогда открывается окно ввода-редактирования текстовых примечаний, которыми вы, возможно, захотите снабдить текущую клетку, например, сделать (для памяти) разъяснения к формуле.

 

 

Пиктограммы для работы с примечаниями Пример примечания

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

Если в процессе работы с таблицей вы захотите изменить текст примечания, или размер, или удалить его совсем, в контекстном меню следует выбрать пункты Изменить примечание или Удалить примечание. В процессе работы может возникнуть желание сделать данное примечание (или все примечания листа) постоянно видимыми. Этого можно достичь, используя пункт контекстного меню Отобразить примечание или выполнить соответствующие настройки (Сервис+Параметры+Вид). Удобство примечаний заключается в том, что они могут располагаться где угодно и занимать сколько угодно места поверх таблицы. Напечатать примечания возможно используя команду Файл+Параметры страницы+Лист+Примечание. Здесь в списке опций можно выбрать вариант В конце листа или Как на листе. В первом случае при печати создается отдельный лист, где будут перечислены адреса ячеек, содержащих примечания и сами примечания, например в следующем виде:

Ячейка: C26

Примечание: Премия.

Это неудобно поскольку при печати адресная сетка таблицы обычно не выводится и тогда очень трудно найти соответствующую ячейку (здесь С26). При установке Как на листе примечания выводятся там, где вы их расположили. При этом, конечно, они должны быть показаны (т.е. установлен режим Отобразить примечание).

Формат по образцу. Оформление таблицы обычно отнимает довольно много времени и внимания пользователя. Ускорить этот рутинный процесс во многих случаях можно воспользовавшись средствами копирования форматов, которые доступны через пиктограмму с изображенной на ней кистью. Для этого следует выделить блок, формат которого копируется (блок-образец), щелкнуть пиктограмму копирования форматов (курсор примет форму кисти) и щелкнуть мышью на форматируемом блоке. Этот блок приобретет формат образца. Если нужно повторить формат еще раз, придется выполнить перечисленные действия повторно. Этого можно избежать, если сделать не одинарный, а двойной щелчок по пиктограмме-кисти, которая в этом случае останется “притопленной”. Далее все последующие щелчки в клетках внутри таблицы повлекут установление в них формата-образца. Отказаться от режима копирования форматов можно новым щелчком по пиктограмме-кисти или клавишей Escape.

Замечание. Установление форматов (денежный, процентный, объединение клеток и др.) с помощью экранных кнопок осуществляется очень легко, но повторное нажатие на них не снимает (как бы нам хотелось) эти форматы. Их можно удалить через главное меню, что довольно трудоемко. Однако есть и более простой способ – копирование желаемых форматов из других клеток с помощью иконки Формат по образцу (при этом старый формат будет заменен на новый). В качестве такой клетки-образца может выступать даже пустая ячейка поскольку она еще не “засорена” какими-либо форматами, т.е. имеет формат Общий.

Защита данных. Здесь следует различать скрытие (данные не видны) и блокировку (данные недоступны для изменения и возможно не видны).

Скрытие отдельных клеток/блоков возможно: а). временным установлением в них формата запрета вывода (;; или;;;), б). установлением белого цвета символов в клетке (будет белый-на-белом). Однако содержимое клеток все равно можно будет просматривать в строке формул. Скрытие строк/столбцов осуществляется командой Скрыть из главного или контекстного меню. Оба эти средства могут быть позже отменены самом владельцем данных или, что хуже, посторонним лицом (если оно умеет это делать). Описанные средства, однако, не бесполезны, если вы хотите временно убрать (только с глаз) некоторые данные, чтобы они не отвлекали от решения конкретной задачи, или вы не хотите их отображать при печати.

Блокировка данных позволяет запретить какое-либо изменение данных и осуществляется через меню Формат+Ячейки+Защита, где можно выбрать опции Защищаемая ячейка и/или Скрыть формулы. Первая из них запрещает изменение пользователем содержимого выделенных блоков, а вторая запрещает и показ формул в строке формул. Установки защиты данных вступают в силу не немедленно, а только после команды Сервис+ Защита+Защитить лист/книгу, где можно установить, при желании, и пароль доступа, что предотвратит несанкционированный доступ к данным.

Масштабирование изображения. В заключение упомянем еще одну полезную кнопку Масштаб, которая делает более удобным обозрение таблиц. Это пиктограмма управления масштабом отображения данных. Здесь можно выбрать один из предлагаемых стандартных (200%, 100%, 75%, 59%, 25%) масштабов, или ввести нужный вручную (например, 120% или 90%), или выбрать пункт По выделению, который выводит в максимально возможном размере выделенный блок клеток таблицы.

 

3.6. Задачи для самостоятельного решения

Контрольные вопросы

1. Для чего нужно форматирование данных?

2. Перечислите средства выравнивания данных.

3. Как объединить ячейки?

4. Перечислите параметры шрифтов.

5. Какие следствия возникают в результате применения к числу знака %?

6. Что такое условное форматирование? Сколько и каких секций может быть в условном формате?

7. Как сделать, чтобы в дате отображалось имя месяца и дня недели?

8. Зачем нужен Мастер условного форматирования?

9. Для чего нужна проверка ввода и где находятся эти средства?

10. Перечислите три уровня контроля ввода данных.

11. Как скопировать в таблице только форматы?

Упражнения

1. Введите в клетку С2 слово Петр. Последовательно сделайте его выравнивание к правой и левой границам и к центру клетки. Расположите слово вертикально и под другим углом.

2. Вернитесь к исходному виду клетки С2. Объедините ячейки С2 и D2 и отцентрируйте здесь слово Петр.

3. Установите в клетке шрифт Courier New, жирный, курсив, размером 18 пунктов.

4. Введите в клетку В1 число 2468,637 и скопируйте его в нижние 18 клеток.

В2	2468,63700		В8	0002468,7		В14	3.Октябрь.1906
В3	2 469		В9	*********2 469		В15	3.10
В4	2 468,64р.		В10	2,47E+03		В16	3.10.06
В5	246864%		В11	вес 2469 кг		В17	3.среда.Октябрь
В6	2 468,64 $		В12	2 тыс.		В18	03.10.06 15:17
В7	$ 2468,6		В13	2468 7/11		В19	15:17:17

Установите для них вид, показанный в таблице используя как стандартные, так созданные вами форматы.

5. Создать (через меню Формат+Ячейка+вкладка Число) условный формат, следующим образом реагирующий на данные в зависимости от диапазона, в котором они находятся:

– если число (например, 0,253) меньше 1 – выводятся два дробных разряда со словом грамм (0,25грамм) синего цвета,

– если число (например, 637,58) больше 1, но меньше 1000 – выводится только целая его часть со словом кг (638кг) синего цвета,

– если число (например, 263124,58) больше 1000 – выводятся только тысячи со словом тонн (263тонн),

– если по ошибке в клетку введено символьное значение, например пробел, – выводится слово ОШИБКА красного цвета.

6. С помощью Мастера условного форматирования построить следующий условный формат для любой текущей клетки:

– если число четное (делится без остатка на 2) и больше нуля – его значение выводится жирным шрифтом на красном фоне,

– если число меньше 1 – курсивом на синем фоне.

7. С помощью средств проверки ввода разрешить ввод в клетку только чисел в диапазоне от 10 до 100 000.

8. С помощью средств проверки разрешить ввод в клетку только данных из списка, содержащего наименования подразделений (Кадры, Дирекция, Касса, Связь, Охрана) организации.

Тесты (выберите ответ)

1. В чем измеряется размер символов? – в пикселях, в миллиметрах, в пунктах.

2. По умолчанию текст выравнивается? – к правой границе, к левой границе, по центру.

3. По умолчанию числа выравниваются? – к правой границе, к левой границе, по центру.

 

ФУНКЦИИ РАБОЧЕГО ЛИСТА

Главным инструментом при работе с электронными таблицами являются клеточные функции. Напомним, что ввод адресов и ключевых слов, используемых в функциях, можно осуществлять как заглавными, так и строчными буквами. Если выражение не содержит ошибок, после нажатия клавиши Enter они автоматически преобразуются в заглавные. Ввиду этого, лучше вводить такие данные строчными буквами, что позволит быстро выявить допущенные синтаксические ошибки. Непреобразование их в заглавные будет означать, что соответствующий элемент введен неверно. Напомним также, что при вводе всем функциям должен предшествовать знак “=”. Если функция получается очень длинной, удобно разбивать ее на отдельные строки внутри клетки, используя клавиши Alt+Enter. Некоторую помощь при вводе формул пользователю может оказать Мастер функций (кнопка “ = ” в строке формул) дающий возможность поиска и формирования нуж­ных функций рабочего листа (здесь не рассматривается).

Замечания.

1. Некоторые из перечисленных функций не входят в ядро Excel и становятся доступны только при загрузке дополнительного Пакета анализа из меню Сервис+Надстройки. Все они помечены в тексте двумя галочками.

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

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

Функция суммирования

В виду особой важности этой функции, ее рассмотрение вынесено в отдельный раздел.

Функция имеет следующий формат:

üСУММ(число1; число2;...) – возвращает сумму чисел, входящих в список аргументов. Список может содержать до 30 элементов. Если в суммируемом блоке встречаются нечисловые элементы, они учитываются как нули.

Примеры: СУММ(3;2)=3+2=5, СУММ(A1;2;A2:C3)=A1+2+A2+B2+C2+A3+B3+C3.

Выражение, содержащее функцию суммирования, можно ввести с клавиатуры, а можно с помощью пиктограммы Автосуммирование å (см. раздел 2.4) или комбинации клавиш Alt + =.

Функция суммирования аналогична простому перечислению слагаемых со знаком плюс. Следующие выражения в Excel совершенно эквивалентны: =СУММ(A1:A3) и =A1+A2+A3. В каких же случаях следует пользоваться функцией суммирования, а в каких сложением? Функция СУММ() удобна, если слагаемых много и они находятся в смежных ячейках. Очевидно, гораздо проще набрать выражение СУММ(D1:D400), нежели адреса четырехсот слагаемых D1+D2+… + D400. Кроме того, при большом числе элементов это может оказаться невозможным, поскольку длина клеточного выражения ограничена. Имеется еще одно отличие. Если среди слагаемых имеется нечисловой элемент, он будет пропущен функцией СУММ(…), но приведет к ошибке при сложении через знак плюс. Другое удобство заключается в том, что функция автоматически реагирует на изменение (включение/удаление строк в диапазоне суммирования) размеров таблицы. На рис. 4.1-1 приведены результаты суммирования данных из области А1:А2 в числовом и формульном виде с использованием обеих формул. Они одинаковы (результат равен 6).

 

A

B

A

B

A

B

A

B

=A1+A2

=СУММ(A1:A2)

Рис. 4.1-1

Рис. 4.1-2

=A1+A2

=СУММ(A1:A3)

 

На рис. 4.1-2 в диапазон суммирования между первой и второй строками вставлена строка, содержащая цифру 5. Видим, что результат, полученный с помощью функции СУММ(), правильный, поскольку функция автоматически учла новое слагаемое (выражение =СУММ(A1:A2) изменилось на =СУММ(A1:A3)). Формула же =А1+А2 осталась прежней и, как следствие, результат неверный. Такое адаптивное поведение вообще характерно для любых функций Excel – они, в основном, правильно учитывают изменения в структуре таблиц.

Однако здесь имеются два исключения – вставка строки непосредственно над областью обработки (*) и непосредственно над строкой (**), содержащей суммы (рис. 4.1-3).

		A			A
		Заголовок			Заголовок
					вставка*
		=СУММ(A2:A3)
					вставка**
Рис. 4.1-3					=СУММ(A3:A4)

Функция СУММ() не откликается на эти действия, продолжая считать своими аргументами только те клетки, которые были аргументами и до вставок (на рисунке заштрихованы), игнорируя новые строки. Таким образом, пользователю в каждом подобном случае предстоит редактировать итоговую строку, что, конечно, очень обременительно. В нашем примере в А6 придется изменить выражение =СУММ(A3:A4) на =СУММ(A2:A5).

Что можно предпринять для учета этих обстоятельств? Нужно сделать так, чтобы крайние верхняя и нижняя строки перестали быть таковыми.

1. Неудобств, связанных с вставкой данных над областью суммирования, можно легко избежать, если включить в нее заголовок столбца, т.е. изменить формулу в A4 на =СУММ(A1:A3). Такой заголовок обязательно должен быть нечислового типа, иначе он будет участвовать в суммировании. Если же он содержит числа, можно поступить иначе – непосредственно под заголовком, но выше собственно области данных включить пустую строку, участвующую в суммирования. Если затем такую строку скрыть, то внесение новой строки вверху видимого диапазона суммирования повлечет правильную автоматическую коррекцию итоговой функции.

2. Для корректной вставки строки непосредственно над строкой, где формируется сумма, также целесообразно иметь пустую строку, включенную в область суммирования. В этом случае добавление новой строки над суммой (точнее над пустой строкой) не потребует какой-либо правки итоговой функции. Неудобством является сама пустая строка – она может попасть в готовый напечатанный документ.

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

G

Начиная с версии Excel-2000 предусмотрен механизм, во многом снимающий эту проблему. Соответствующая установка имеется в меню Сервис+Параметры+ вкладка Правка. Если здесь установить флажок Расширять форматы и формулы в списках, то дополнение таблицы в любом ее месте влечет автоматическое копирование в новую строку/столбец и форматов и формул из предыдущих строк/столбцов. Чтобы это свойство проявилось нужно только, чтобы повторяющиеся форматы и формулы были, по крайней мере, в трех из пяти предшествующих рядах таблицы. При этом не только автоматически добавляются формулы в заполняемой строке, но и корректируются итоговые выражения, находящиеся под/справа таблицы. Однако следует отметить, что указанный механизм может не работать в случаях сложных итоговых функций и в самом начале таблицы.

Арифметические функции

ü ABS(число) – абсолютное значение числа. Примеры: ABS(7)=ABS(-7)=7.

ü ЗНАК(число) – функция определения знака числа. Если аргумент больше 0, возвращается 1, если ноль – то 0, если отрицательное – то -1. Примеры: ЗНАК(3)=1, ЗНАК(0)=0, ЗНАК(-12)=-1.

ü ОСТАТ(делимое; делитель) – возвращает целочисленный остаток от деления двух чисел. Если делимое меньше делителя, результат равен делимому. Если деление выполняется без остатка, результат – ноль. Примеры: ОСТАТ(9;4)=1, ОСТАТ(5;11)=5, ОСТАТ(8;2)=0.

Функция определяет кратность одной величины другой. Так, например, при продаже товара общая сумма покупки должна быть кратна цене единицы товара, т.е. следует контролировать факт того, что

ОСТАТ(сумма;цена)=0.

ü ПРОИЗВЕД(число1; число2;...) – возвращает произведение чисел в списке аргументов. Если во множестве аргументов встречаются нечисловые элементы, они в произведении не участвуют (пропускаются).

Примеры: ПРОИЗВЕД(3;2;4)=3*2*4=24,

ПРОИЗВЕД(A1;2;A2:C3)=A1*2*A2*B2*C2*A3*B3*C3.

Функции округления

ü ОКРУГЛ(число; десятичные знаки) – функция округляет число до указанного количества десятичных знаков. Второй аргумент может быть положительным (округление происходит справа от десятичной запятой) и отрицательным (округление слева от запятой – младшие целые разряды округляются до нулей).

Примеры: ОКРУГЛ(512,4567;2)=512,46, ОКРУГЛ(512,4567;-2)=500.

Замечание. Функции округления и операции форматирования данных внешне могут привести к одному и тому же результату. Следует иметь в виду, что форматирование изменяет только вид числа, а округление фактически меняет это число (в примере число 512,4567 действительно превратилось в 500). Очевидно, что Excel обрабатывает не те значения, которые видит пользователь на экране, а те, что хранятся в его памяти, которые могут и отличаться между собой.

ü ОКРВНИЗ(число; множитель) – округляет заданное число до ближайшего меньшего числа, кратного второму аргументу. Примеры: ОКРВНИЗ(23,4;0,5)=23, ОКРВНИЗ(5;3)=3, ОКРВНИЗ(5;1,5)=4,5.

ü ОКРВВЕРХ(число; множитель) – округляет заданное чис­ло до ближайшего большего числа, кратного второму аргументу. Примеры: ОКРВВЕРХ(23,4;5)=25, ОКРВВЕРХ(5;3)=6, ОКРВВЕРХ(5;1,5)=6.

Функции ОКРВВЕРХ() и ОКРВНИЗ() удобны, например, в случае, когда нужно отпустить товар, расфасованным в тару фиксированной емкости (например, 150 единиц в ящике). Положим, поступил заказ на 1000 единиц. Тогда возможны два варианта (с избытком или с недостатком)

ОКРВВЕРХ(1000;150)=1050штук или ОКРВНИЗ(1000;150)=900штук,

которые и предлагаются покупателю.

ü ОКРУГЛВНИЗ(число; количество цифр) – округляет число по модулю до ближайшего меньшего целого с заданным количеством цифр. Если параметр количество цифр равен 0 или опущен, число ок­ругляется до ближайшего меньшего целого. Если количество цифр >0, число округляется до заданного количества десятичных разрядов после запятой. Если <0, целая часть числа округляется слева от десятичной запятой на заданное количество цифр.

Примеры: ОКРУГЛВНИЗ(82599,8;-2)=82500, ОКРУГЛВНИЗ(567,8176;2)=567,81, ОКРУГЛВНИЗ(567,987;)=567.

ü ОКРУГЛВВЕРХ(число; количество цифр) – округляет число по модулю до ближайшего большего целого с заданным количеством цифр.

Примеры: ОКРУГЛВВЕРХ(82501,8;-2)=82600, ОКРУГЛВВЕРХ(567,8123;2)=567,82, ОКРУГЛВВЕРХ(567,001;)=568.

ü ЦЕЛОЕ(число) – функция возвращает ближайшее снизу целое число от аргумента.

Примеры: ЦЕЛОЕ(5,8)=5, ЦЕЛОЕ(-5,8)=-6.

ü ОТБР(число) – отбрасывает все дробные разряды числа. Примеры: ОТБР(5,8)=5, ОТБР(-5,8)=-5.

ü ЧЁТН(число) – округляет число до ближайшего четного.

Примеры: ЧЁТН(23,4)=24, ЧЁТН(2)=2, ЧЁТН(-3)=-4, ЧЁТН(3)=4.

ü НЕЧЁТ(число) – округляет число до ближайшего нечетного.

Примеры: НЕЧЁТ(23,4)=25, НЕЧЁТ(3)=3, НЕЧЁТ(4)=5, НЕЧЁТ(-4)=-5.

Обе функции ЧЁТН() и НЕЧЁТ() положительные значения округляют вверх, а отрицательные – вниз (напомним, что ноль число четное).

Степенные функции

ü КОРЕНЬ(число) – извлекает квадратный корень из положительного числа. Пример: КОРЕНЬ(4)=2.

ü СТЕПЕНЬ(число; степень) – возводит число в заданную степень. Пример: СТЕПЕНЬ(3;2)=32=9.

ü EXP(степень) – возвращает результат возведения в степень основания натурального логарифма (е =2,71878...). Пример: EXP(2)= е 2=7,389.

ü LOG10(число) – вычисляет десятичный логарифм числа.

ü LOG(число; основание) – вычисляет логарифм числа по заданному основанию.

ü LN(число) – вычисляет натуральный логарифм числа. Функция является обратной по отношению к функции EXP(). Примеры: LOG10(1000)=3, LOG(32;2)=5, LN(EXP(4))=4.

Тригонометрические функции

ü ПИ() – возвращает значение константы p=3,14159265358979.

ü ГРАДУСЫ(угол в радианах) – преобразует радианную меру угла в градусную.

ü РАДИАНЫ(угол в градусах) – преобразует градуснуюмеруугла в радианную.

ü SIN(угол в радианах) – синус угла.

ü COS(угол в радианах) – косинус угла.

ü TAN(угол в радианах) – тангенс угла.

ü ASIN(число) – арксинус числа. Угол возвращается в радианах.

ü ACOS(число) – арккосинус числа. Результат в радианах.

ü ATAN(число) – арктангенс числа. Результат в радианах.

ü ATAN2(X;Y) – арктангенс для заданных координат точки X и Y.

Положительный результат соответствует отсчету угла против часовой стрелки относительно оси Х, отрицательный – по часовой стрелке. Функция ATAN(y/x) аналогична ATAN2(x;y), но в последней аргумент Х может быть равен 0. Примеры: SIN(РАДИАНЫ(90))=1, SIN(ПИ()/2)=1, ГРАДУСЫ(ATAN(1))=45.

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

Текстовые функции

ü ЗНАЧЕН(текст) – превращает текстовое отображение числа в числовое.

Пример: ЗНАЧЕН("1232,56")=1232,56.

ü ТЕКСТ(число; формат) – преобразует число в текст, учитывая заданный формат представления.

Примеры: ТЕКСТ(2,715; "00,00")="02,72", ТЕКСТ("25.3.98";"ДДДД")="Среда".

ü ФИКСИРОВАННЫЙ(число[; количество десятичных знаков])

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

Пример: ФИКСИРОВАННЫЙ(12345,678; 2)="12345,68", ФИКСИРОВАННЫЙ(12345,678; -3)="12000".

ü ДЛСТР(текст) – возвращает длину текстовой строки. Пример: ДЛСТР("Клавиша Enter")=13.

ü СЖПРОБЕЛЫ(текст) – функция удаляет из текстовой строки концевые пробелы и уменьшает число пробелов внутри строк (если есть) до одного.

Примеры: СЖПРОБЕЛЫ(" 1 Мая ")= "1 Мая", СЖПРОБЕЛЫ(" Пере ")&"кресток"= "Перекресток".

ü СОВПАД(текст1; текст2) – проверяет совпадают ли два аргумента. Если да, результатом является ИСТИНА, иначе – ЛОЖЬ. Здесь подразумевается полное совпадение, включая содержание и длину. Прописные и строчные буквы считаются разными буквами.

Примеры: СОВПАД("Саша";"Саша")= ИСТИНА, СОВПАД("Саша";"cаша")= ЛОЖЬ,

СОВПАД("Саша";"Саша ")= ЛОЖЬ.

ü ЗАМЕНИТЬ(исходный текст; начальная позиция замены; число заменяемых символов в исходном тексте; текст замены) – функция заменяет часть исходной текстовой строки на новую текстовую строку (текст замены) с заданной начальной позиции.

Примеры: ЗАМЕНИТЬ("1234567"; 3; 4; "*")=12*7, ЗАМЕНИТЬ("1234567"; 1; 9; "*")=*,

ЗАМЕНИТЬ("1234567"; 4; 0; "*")=123*4567,

ЗАМЕНИТЬ("1234567"; 4; 0; " меньше ")=123 меньше 4567.

ü НАЙТИ(искомый текст; текст, где ведется поиск [; начальная позиция поиска]) – возвращает номер позиции, где искомый фрагмент найден в просматриваемом тексте. Необязательный параметр начальная позиция задает номер в искомом фрагменте, с которого следует вести поиск. Если параметр начальная позиция опущен, он считается равным 1 (поиск ведется с первого символа).

Пример: НАЙТИ("Петропавловский";"павлов")=6.

ü ПОИСК(искомый текст; текст, где ведется поиск [;начальная позиция]) – функция аналогична предыдущей, однако допускает включение в искомый текст символов шаблона (? обозначает один произвольный символ и * обозначает любое число произвольных символов). Применение шаблона удобно при поиске по неточно заданному ключу. Например, вы хотите выяснить факт нахождения в клетке С12 фамилии, начинающейся на букву И с инициалами А.В. Функция ПОИСК("И*А.В";C12) вернет значение 1, если в анализируемой ячейке находится, например, текст Иванов А.В. и значение ошибки #ЗНАЧ!, если поиск неуспешный.

ü ПРАВСИМВ(текст; длина фрагмента) – функция извлекает с правой стороны текста фрагмент заданной длины. Пример: ПРАВСИМВ("в нашем случае";6)= "случае".

ü ЛЕВСИМВ(текст; длина фрагмента) – функция извлекает с левой стороны текста фрагмент заданной длины. Пример: ЛЕВСИМВ("в нашем случае";7)= "в нашем".

ü ПСТР(текст; номер первого символа; длина фрагмента) – функция извлекает из текстовой строки фрагмент, начиная с заданного номера указанной длины. Отсчет номера позиции ведется слева направо. Если длина оказалась больше, чем остаток текста, результатом явится вся оставшаяся часть текста. Пример: ПСТР("в нашем случае";3;5)= "нашем".

ü СЦЕПИТЬ(текст1; текст2;...) – возвращает строку, образованную соединением всех перечисленных аргументов. Действие функции аналогично применению оператора &. Функция работает и с числовыми значениями, воспринимая их как символы.

Пример: СЦЕПИТЬ("Саша ";"Петров")="Саша Петров", СЦЕПИТЬ(3;5,81)="35,81"

ü СТРОЧН(текст) – преобразует буквы текста в строчные. Пример: СТРОЧН("А. Петров")="а. петров".

ü ПРОПИСН(текст) – преобразует буквы текста в прописные.

Пример: ПРОПИСН("А. Петров")= "А. ПЕТРОВ".

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

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

ü ЕСЛИ(условие; результат, если условие истинно; [результат, если условие ложно]) – функция оценивает логическое условие, и если оно истинно, возвращается первый результат, если нет – второй.

Пример: Следующая функция в зависимости от результата сравнения содержимого числовых клеток А1 и В5 выдает первое "A1 больше B5" или второе "A1 меньше или равно B5" сообщение в клетке, куда была внесена

=ЕСЛИ(A1>B5; "A1 больше B5";"A1 меньше или равно B5").

Здесь результатом является выражение текстового типа.

Пример. Пусть требуется выяснить не какое из чисел А1, В5 является большим, а показать само это число

=ЕСЛИ(A1>B5; A1; B5).

Результатом будет значение числового типа. Очевидно, что его можно использовать в качестве слагаемого, сомножимого и т.п. Так, корректным будет выражение вида =5*ЕСЛИ(A1>B5;A1;B5)-24.

В функцию ЕСЛИ() могут включаться другие такие функции (до семи) для формирования более сложных условий. Положим, имеются три клетки А1, А2, А3, о которых нужно выяснить, правда ли, что значение А1 больше остальных. Эту функцию реализует следующая строка:

=ЕСЛИ(A1>B1;ЕСЛИ(A1>C1; "A1 самое большое";

 

"А1 не самое большое"); "А1 не самое большое").

Параметр результат, если условие ложно в функции ЕСЛИ() может быть опущен. Тогда, если результат, полученный при анализе условия, ложен, функция вырабатывает логическое значение ЛОЖЬ. Обычно такого не следует допускать, но в некоторых случаях это позволяет немного упростить функцию ЕСЛИ(). Так, например, если она участвует в операции сложения, значение ЛОЖЬ интерпретируется как 0.

 

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

ü И(условие; условие;...) – возвращает значение ИСТИНА, если истинны все аргументы. Функция еще называется функцией логического умножения.

ü ИЛИ(условие; условие;...) – возвращает значение ИСТИНА, если истинен хотя бы один из аргументов. Функция еще называется функцией логического сложения.

ü НЕ(условие) – возвращает значение ИСТИНА, если ложен аргумент и наоборот. Функция еще называется функцией отрицания или инверсии, поскольку ее значение всегда противоположно значению аргумента.

Пример. Выяснить, правда ли, что А1 больше остальных (решение предыдущей задачи):

ЕСЛИ(И(A1>A2;A1>A3);"A1 самое большое";"A1 не самое большое").

Пример: Выяснить, правда ли, что среди клеток есть отрицательные.

ЕСЛИ(ИЛИ(A2<0; A1<0; A3<0); "Правда"; "Неправда").

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

Функции И(), ИЛИ(), НЕ() по смыслу, в общем, отвечают соответствующим союзам в русском языке. Некоторые замечания следует сделать в отношении функции ИЛИ(). В обычном предложении слово ИЛИ может быть как объединяющим, так и разделяющим союзом в зависимости от контекста, в котором оно используется. Объединяющее ИЛИ определяет событие, которое произойдет, если имеет место хотя бы одно из перечисленных условий (в том числе, одновременно несколько из них, например, все условия сразу). Именно такой смысл имеет функция ИЛИ(), описанная выше. Разделяющее ИЛИ определяет событие, которое произойдет, если наблюдается только строго одно из условий-участников, но не несколько сразу. Если нам все-таки нужно описать условие, соответствующее разделяющему ИЛИ, нужно сформировать логическую функцию вида

ИЛИ(И(условие1; НЕ(условие2));И(НЕ(условие1);условие2)).

Пусть требуется сформировать конкретную функцию, выявляющую факт того, что только строго одна из клеток А1 или А2 больше 0

ЕСЛИ(ИЛИ(И(A1>0;НЕ(A2>0));И(НЕ(A1>0);A2>0));"ДА";"НЕТ")

или несколько проще

ЕСЛИ(ИЛИ(И(A1>0;A2<=0);И(A1<=0;A2>0));"ДА";"НЕТ"). Функция формирует ответ вида ДА/НЕТ.

Замечание. Часто вместо функций И, ИЛИ удобно использовать операции умножения и сложения. Так для предыдущей задачи выявления того, что строго одна из клеток А1 или А2 больше 0 можно записать

=ЕСЛИ((A1>0)*(A2<=0)+(A1<=0)*(A2>0);"ДА";"НЕТ").

Здесь, если первые два условия удовлетворяются, формула приведет нас к результату следующего вида

=ЕСЛИ(1*1+0*0;"ДА";"НЕТ")=ЕСЛИ(1;"ДА";"НЕТ")="ДА",

Так как 1 (и вообще любое число не равное нулю) интерпретируется Excel как ИСТИНА ответом будет слово ДА.

Пример. Рассмотрим аналогичный, но более сложный пример. Пусть нам требуется выяснить факт того, что только одна из четырех ячеек (А1, А2, А3, А4) больше нуля. Очевидное решение заключается в том, что перебираются все возможные комбинации аргументов, в которых только одно из значений больше нуля

=ЕСЛИ(ИЛИ(И(A1>0;A2<=0;A3<=0;A4<=0); И(A1<=0;A2>0;A3<=0;A4<=0);

И(A1<=0;A2<=0;A3>0;A4<=0); И(A1<=0;A2<=0;A3<=0;A4>0)); "Правда"; "Неправда").

Получилось громоздко. Часто, однако, можно упростить решение, если подходить к нему “не в лоб”. Здесь лучше не анализировать каждую из четырех возможных комбинаций значений этих ячеек, а воспользоваться некоторым косвенным признаком, – для каждой из ячеек выяснить, больше ли она нуля, и, если больше, в качестве результата выработать 1 (например, ЕСЛИ(А1>0;1)). Теперь, если результат сложения таких элементарных выражений в точности равен 1, значит только одна из ячеек больше нуля. Тогда запишем:

=ЕСЛИ(ЕСЛИ(A1>0;1)+ЕСЛИ(A2>0;1)+ЕСЛИ(A3>0;1)+ЕСЛИ(A4>0;1)=1; "Правда";"Неправда").

Третий аргумент внутренних функций ЕСЛИ() опущен поскольку, как уже говорилось, если анализируемое условие ложно, будет выработано значение ЛОЖЬ, которое в цепочке сложений будет восприниматься как ноль. Можно еще сократить функцию, оставив от внутренних ЕСЛИ() только одни условия. Окончательно

=ЕСЛИ((A1>0)+(A2>0)+(A3>0)+(A4>0)=1;"Правда";"Неправда").

Рассмотрим пример “из жизни”.

Пример. Положим, требуется построить таблицу выявления факта пригодности призывников к военной службе (Годен/Не годен), например, к зачислению в летное училище. Пусть это невозможно для лиц, имеющих недостаточные/избыточные значения антропометрических параметров (рост и вес). Положим, отклоняются кандидатуры ростом менее 150 см или более 200 см, а также с весом менее 55 кг или свыше 100 кг. Иными словами

=ЕСЛИ(рост<150 ИЛИ рост>200 ИЛИ вес <55 ИЛИ вес>100, то Не годен, иначе Годен).

Или в виде формулы для первой строки таблицы на рис. 4.7-1.

D2=ЕСЛИ(ИЛИ(B2<150; B2>200; C2<55; C2>100);"Не годен";"Годен"),

Аналогичные результаты можно получить с помощью логической функции, в некотором смысле обратной к исходной. Для этого следует все функции И() заменить на ИЛИ() и, наоборот, перевернуть условия на противоположные и поменять местами вырабатываемые результаты (Годен/Нет):

D2=ЕСЛИ(И(B2>=150;B2<=200;C2>=55;C2<=100);"Годен";"Нет")

	A	B	C	D
	Призывник	Рост	Вес	Отбор
	Иван			Не годен