Работа с автозаполнением, составление формул, абсолютные и относительные ссылки на ячейки.

Краткие теоретические сведения

В процессах обработки данных нередки ситуации, когда в зависимости от условий следует выполнять либо одну, либо другую операцию. Известный пример: при определении подоходного налога мы должны исходить из суммы заработной платы, величина которой определяет порядок расчетов.

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

ЕСЛИ (условие;формула1;формула2),

где: условие — логическое выражение, например: С7 < 3 000 000;

формула1 — действие, которое будет выполнять табличный процессор в случае, если условие выполняется;

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

Например, чтобы разместить в ячейке С12 максимальное из двух значений, содержащихся в ячейках С10 и С11, достаточно ввести в ячейку С12 формулу:

=ЕСЛИ(С10>С11;С10;С11).

Часто приходится использовать сложные условия и вложенные логические функции, например: в ячейке A1 содержится дата выполнения расчета. Требуется в заголовке рабочей таблицы (например, в ячейке В4) указать номер квартала. Очевидно, что номер квартала определяется в зависимости от месяца, а для выполнение поставленной задачи потребуется формула:

«ЕСЛИ(И(А1>=1; А1<=3); "1-й квартал"; ЕСЛИ(И((А1>3; А1<=6); "2-й квартал"; ЕСЛИ(И(А1>6; А1<=9); "3-й квартал");"4-й квартал")));

Задание.

Создайте электронную таблицу учета платы за квартиру согласно образцу.

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

2. Столбец «№ квартиры»: 10, 11, 12, ... 50. Центральное выравнивание.

3. Столбец «Фамилия квартиросъемщика»: Иванов, Петров, Сидоров, Краснов, Белов, все остальные Куропаткин 1, Куропаткин 2, ...

4. Столбец «Площадь»:70; 69,5; 69 и т. д. (каждая следующая на 0,5 кв. м меньше предыдущей), выравнивание центральное.

5. Столбец «Сумма»: для каждой квартиры умножается значение из графы «Площадь» на значение из графы «Тариф»(которое может быть изменено преподавателем во время работы); формат рублевый без копеек.

6. Столбец «Дата оплаты»: с 10 февраля, каждая следующая квартира произвела оплату на день позже предыдущей. Формат Дата, полная форма.

7. Столбец «Просрочка»; если оплата была произведена до срока оплаты включительно, то автоматически ставится 0 (ноль); если позже срока, то должно подсчитываться количество дней просрочки («Дата оплаты» — «Срок оплаты»). Срок оплаты может меняться преподавателем во время работы с вашей таблицей. Данные выровняйте по центру.

8. Столбец «Штраф»: для каждой квартиры умножается значение из графы «Пени за 1 день» на значение из графы «Просрочки». Формат денежный без копеек.

9. Столбец «Итого»: суммируются значения из граф «Сумма» и «Штраф»,формат денежный без копеек.

10. В конце ведомости должна автоматически подсчитываться следующая статистика по всем квартирам: общая сумма графы «Итого» (формат рублевый без копеек), Средняя площадь, Максимальная просрочка.

Образец выполнения задания

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

1. Опишите значение параметров логической функции ЕСЛИ?
2. Какие ссылки на ячейки являются относительными, а какие абсолютными?

Лабораторная работа № 4

Составление условий в электронных таблицах.

Задание 1.

Создайте на рабочем листе таблицу «Примеры условий».

Проверьте работу условий.

1. В ячейку С2 введите большее из значений, содержащихся в соседних слева ячейках.

2. В ячейку С3 введите слово «Да», если значение ячейки A3 больше значения ячейки В3. В противном случае введите слово «Нет».

3. Создайте формулу, записывающую в ячейку С4 слово «Да», если ячейка А4 пуста или содержит текст. В противном случае в ячейке C4 должно появляться слово «Нет».

4. Сформируйте формулу, определяющую значение ячейки С5 как:

• 1, если содержимое ячейки В5 меньше 0;

• 0, если содержимое B5 равно нулю;

• формулу A5/B5, если В5 больше нуля.

Задание 2.

1. Составьте электронную таблицу следующего вида, для решения любого квадратного уравнения с помощью дискриминанта:

2. Запишите в соответствующие ячейки формулы и условия для вычисления дискриминанта и корней уравнения со ссылкой на ячейки с исходными данными.

Квадратное уравнение — это уравнение вида ax² + bx + c = 0, где a не равно 0.

Для решения квадратного уравнения можно использовать формулы:

и

где D = b² - 4ac — дискриминант многочлена ax² + bx + c. Если D > 0, то уравнение имеет два различных вещественных корня. Если D = 0, то оба корня вещественны и равны. Если D < 0, то оба корня являются комплексными числами.

Проверьте работу таблицы на следующих примерах:

5х² - 6х + 1 = 0 (два корня 1; 0,2)

16х² -8х+ 1=0 (один корень 0,25)

Зх² - х + 18 = 0 (корней нет).

Образец выполнения задания

Задание 3.

Начисление налогов с использованием условий

1. Откройте таблицу «Расходные материалы (картриджи)» (файл с именем л4.хls).

2. Выберите для столбцов нужную ширину и формат.

3. Занесите в верхней части таблицы курс доллара по образцу.

4. Заполните таблицу формулами:

- стоимость заказа в $ и в рублях;

- НДС вычисляется 20% от стоимости заказа в рублях;

- скидки выбираются по условию в зависимости от количества штук: 5% для кол. штук от 100 до 200; 10% — более 200 штук; для заказа менее 100 штук скидок нет;

- столбец К оплате = Стоимость заказа в рублях - Скидки +НДС;

- в конце таблицы запишите формулу для расчета общей суммы заказа.

5. Оформите таблицу выравниваниями, обрамлениями и цветом.

Образец выполнения задания

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

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

Лабораторная работа № 5