Приветствую всех, дорогие читатели блога TutorExcel.Ru.
- * (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
- ? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.
- ~ (тильда) с последующими знаками *, ? или ~. Обозначает конкретный символ *, ? или ~. Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «~» и искать по фразе «хор~*». Наличие «~» гарантирует, что Excel прочитает следующий символ как текст, а не как подстановочный знак.
Использование таких спецсимволов может быть полезно при фильтрации данных, для сравнения текста, при поиске и замене текстовых значений. Давайте подробно остановимся на каждом из основных вариантов применения.
Ответить на тест 1. в ячейку введены символы сумм(g4: h5). как excel воспримет эту информацию? а))текст б))число 2. в ячейки f6,f7,g6,g7 введены соответств. числа.
Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.
Вложенные ЕСЛИ (несколько условий)
Для построения более сложных проверок в одной формуле можно использовать несколько функций ЕСЛИ(). Для этого в качестве значений аргументов значение_если_истина и значение_если_ложь введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу
- Если значение из ячейки А8 менее 5, то будет выведено 1
- Если значение из ячейки А8 более 10, то будет выведено 3
- Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2
Примечание : В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.
Примечание : Формулу =ЕСЛИ(A8 10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)
Формула =ЕСЛИ(A8 10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8 =5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.
Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:
1.1) они не должны пересекаться. В примере выше — 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8 10;3;ЕСЛИ(A8 10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.
1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.
2) Правильно записывайте логические выражения. Например, условие 5 10;3;2)) в строке формул можно записать как
Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.
4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения — последовательности 1; 2; 3 . Это покажет какой блок вложенных ЕСЛИ() срабатывает.
5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета
=ЕСЛИ(A1>=100;»Очень крупный проект»;
ЕСЛИ(A1>=90;»Крупный проект»;
ЕСЛИ(A1>=50;»Средний проект»;»Малый проект «)))
Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы. Ниже приведено 2 эквивалентные формулы:
Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:
Формула =ПРОСМОТР(A1;;) выглядит необычно, но по сути второй аргумент , как и третий аргумент это запись содержимого 4-х ячеек. Вместо можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.
Еще пример. Пусть требуется вывести разный текст (например » 0″) в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():
Примечание : -1E+307 — это самое маленькое отрицательное число в EXCEL, 1E-307 — это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).
Второй массив можно заменить ссылкой на диапазон, например для того чтобы вывести результат вычисления формул:
Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).
Не суммируются ячейки в Excel?
Если нам не нужно, чтобы адрес пересчитывался при копировании формулы, мы можем его «закрепить» в формуле — поставить знак $ перед буквой и индексом ячейки: =$D200+3. Такой адрес называется абсолютным. Такая формула не будет изменяться при копировании:
ФУНКЦИЯ АВТОМАТИЧЕСКОГО СУММИРОВАНИЯ В EXCEL
Хочу обратить Ваше внимание: в Excel может быть выключена функция суммирования.
В таком случае нужно просто включить вычисления: зайти в Параметры программы, далее выбрать Формулы, затем включить автоматические вычисления в книге.
Задача №7. Электронные таблицы. Абсолютная и относительная адресация. Графики и диаграммы.
- выделяем столбец с цифрами, которые нам нужно поправить.
- затем нажимаем Ctrl+F, вызываем меню “Найти и заменить” (как в предыдущем случае).
- В “Найти” — вставляем скопированный пробел (1), “Заменить” — оставляем пустым, (2) далее нажимаем “Заменить все” (3)
Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1 10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )
