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. Четвертый параметр «интервальный_просмотр» необязателен. Список для просмотра должен быть упорядочен по возрастанию. Просмотр списка до тех пор, пока не будет найдено значение, больше или равное значению аргумента «искомое_значение».