Пользовательские форматы в Excel

При необходимости Вы можете легко добавить к стандартным числовым форматам Excel свои собственные. Для этого выделите ячейки, к которым надо применить пользовательский формат, щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек - вкладка Число, далее - Все форматы:

В появившееся справа поле Тип: введите маску нужного вам формата из последнего столбца этой таблицы:

Как это работает...

Excel использует несколько спецсимволов в масках форматов:

0 (ноль) - одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 - получится 1,35.

# (решетка) - одно необязательное знакоместо - примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится

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

[ ] - в квадратных скобках перед маской формата можно указать цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.

Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) - надо обязательно заключать в кавычки.

Можно указать несколько (до 4-х) разных масок форматов через точку с запятой. Тогда первая из масок будет применяться к ячейке, если число в ней положительное, вторая - если отрицательное, третья - если содержимое ячейки равно нулю и четвертая - если в ячейке не число, а текст (см. выше пример с температурой).

Вопросы для повторения

1. В чем разница между абсолютной адресацией и относительной адресацией в электронной таблице? Как изменить тип ссылки?

2. Как присвоить имя ячейки?

3. К какому типу адресации относится использование имени ячейки?

4. Как можно осуществить быстрый переход по имени ячейки?

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

6. Как присвоить имена нескольким диапазонам?

7. Как присвоить имя формуле?

8. Как вставить имя в формулу? (несколько способов)

Практическая работа №17

Использование логических функции в MS Excel

Цель работы: сформировать навыки использования функции «ЕСЛИ» при создании формул, имеющих несколько условий.

Немного теории

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

Общий вид условной функции следующий:

ЕСЛИ(<условие>; <выражение1>; <выражение2>)

Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ.

<выражение 1> и <выражение 2> могут быть числами, формулами или текстами (текст записывается с помощью кавычек).

Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки определит <выражение 1>, в противном случае — <выражение 2>.

Логические выражения. Логические выражения (условия) строятся с помощью операций отношения (<, >, <=(меньше или равно), >=(больше или рано), =, <>(не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические величины ИСТИНА или ЛОЖЬ.

Существуют особенности записи логических операций в табличных процессорах: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды. Например, логическое выражение И(А1>0; А1<1) соответствует системе неравенств 0 < А1 < 1.

Задание 1. Разработать таблицу, содержащую следующие сведения об абитуриентах: фамилия, оценки за ЕГЭ по математике, русскому и иностранному языку, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу (180) и балл за экзамен по иностранному языку больше 60, то абитуриент зачислен в учебное заведение, в противном случае — нет.

Решение. Исходной информацией являются фамилии, баллы за экзамены и проходной балл. Сумма баллов и информация о зачислении вычисляются с помощью формул.

Рассмотрим таблицу:

В ячейкеС2 будет хранится значение проходного балла – 180.

Оценки абитуриентов подбираются самостоятельно.

Формула в ячейке Е4 вычисляет сумму балов за три экзамена.

Формула в ячейке F4 задается с помощью условной функции:

ЕСЛИ(И(E4>=$C$2; D4>60);"Да"; "Нет")

Условие, записанное с помощью логической И можно расшифровать так: сумма баллов (E4)>= проходному баллу (С2) И количество баллов за экзамен по иностранному языку (D4)> 60. Если условие выполняется, то в F4 отображается текст "Да", в противном случае – "Нет".

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

Таблица должна получиться следующего вида:

Задание 2. Таблица содержит следующие данные об учениках школы: фамилия, возраст и рост ученика. Сколько учеников могут заниматься в секции, если туда принимают детей с ростом не менее 160 см.? Возраст не должен превышать 13 лет.

Формируем таблицу – данные подбираем самостоятельно. Для решения подобной задачи предлагаю следующее: ввести так называемый код зачисления, который равен 1, если учащийся зачислен, и равен 0, если ученик не подходит по параметрам.

В столбец В заносим рост, в столбец С заносим возраст

Формула будет иметь вид =ЕСЛИ(И(B23<=13;C23>=160);1;0)

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

Задание 3. Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10, то – в 1,5 раза. получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена до уценки, цена после уценки.

Данная задача использует сложную структуру в логической функции – используется двойное условие, которое записывается таким образом в ячейку столбца D: =ЕСЛИ(C36>10;B36/2;ЕСЛИ(C36<7;B36;B36/1,5)), где в ячейке (С36) – сколько месяцев хранится товар, а в соответствующей ячейке (В26) – цена до уценки.