12 Вопрос

Использование формул с условиями:

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

Выполнение сложных сравнений:

Например, надо просчитать сумму только в том случае, когда значения в ячейке В7 входят в интервал от 30 до 60, иначе 0.

В данном случае формула = ЕСЛИ(И(В7>30; B7<61); СУММ( );0).

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

= ЕСЛИ(ИЛИ(ИЛИ В12=36; В12=»Ольга»);»ОК»; «_») если ячейка В12 содержитчисло 36, либо строку «Ольга», то выводится текст ОК. В противном случае будет пустая строка.

Проверка данных при вводе.

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

ВРЕМЗНАЧ (время_как_текст) - преобразует время из текстового формата в дату в числовом формате.

Например, на рабочем листе создана форма, при заполнении которой надо проверить следующие условия: Если введенная дата следует послу 1/1/1995, то выводится пустая строка. В противном случае, в ячейке появляется сообщение об ошибке.

Ячейка	Проверяемое условие	Формула
64	Дата после 1/1/1995	= ЕСЛИ(Д4>BРЕМЗНАЧ(“1/1/1995”); “_”; ”Введите дату после 1/1/1995 “)
66	Код товара присутствует в сиске	= ЕСЛИ (ЕНД (ПОИСКПОЗ (D6;К3:К11;0)); “Неверный номер”; “_”)
68	Название подразделения присутствует в списке	= ЕСЛИ (ЕНД (ПОИСПОЗ (D8; L3:L8;0)); “Восток, запад, Юг, Север”; “_”)
610	Диапазон значений	= ЕСЛИ (И (D10>4, D10<21);”_”; “от 5 до 20 единиц”)

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

Функция ПОИСКПОЗ ( ) (МАТСН), СОДЕРЖАЩАЯСЯ В ЯЧЕЙКЕ 66, просматривает значения в ячейках К3:К11, для нахождения элемента, в точности совпадающего с содержанием ячейки D6, что определяет последний аргумент 0. Если требуемого элемента не отыскано, то функция возвращает значение ошибки # Н/Д ! (#N/A!).

При этом функция ЕНД ( ) (ISNA) возвращает значение ИСТИНА, в результате чего выводится сообщение «Неверный номер». Если же элемент найден, отображается пустая строка.

Обратите внимание, что при использовании функции ПОИСКПОЗ ( ) возвращает неопределенное значение, если не найден элемент, в точности совпадающий с заданным значением, тогда как функции ГПР ( ) и ВПР ( ) отыскивают близкий, но неточный результат.

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

Значение объема поставки находится в пределах от 5 до 20 единиц. Таким образом, формула в ячейке G10, содержащая функцию И ( ), выполняет проверку на вхождение указанного числа в данный диапазон. Если условие истинно, то выводится пустая строка. В противном случае, отображается текст “от 5 до 20 единиц.”

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

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

В Excel есть 2 способа просмотра информации в таблицах.

1-ый - применение функций ГПР ( ) и ВПР ( ). Их недостаток: они находят значение, которые точно или приближенно равно искомому только в упорядоченном списке.

2-ый - использованные функции ИНДЕКС ( ) (INDEX) совместно с функцией ПОИСКПОЗ ( ). Список может быть не упорядочнным. Если точного совпадения не найдено, функция возвращает значение ошибки #Н/Д. Этот способ лучше использовать, например, чтобы узнать количество единиц определенного товара, находящихся в распоряжении.

Функции ГПР ( ) и ВПР ( ).

ВПР (искомое_значение; инфо_таблица; номер_столбца; ин тервальный_просмотр).

ГПР (искомое_значение; номер_строки; интерв_просмотр).

ВПР ( ) ищет значение в левом столбце таблицы, из которого возвращается результат поиска.

ГПР ( ) ищет значение в верхней строке.

Первый столбец или строка (в которых осуществляется поиск) всегда имеет N=1. Четвертый параметр «интервальный_просмотр» необязателен. Список для просмотра должен быть упорядочен по возрастанию. Просмотр списка до тех пор, пока не будет найдено значение, больше или равное значению аргумента «искомое_значение».