- •Оглавление
- •Введение
- •1. Сфера применения электронных таблиц
- •2. Организация данных в электронной таблице
- •2.1. Общие сведения
- •2.2. Понятие ссылки. Типы данных
- •2.3. Сообщения об ошибках
- •2.4. Форматы представления данных
- •3. Копирование формул. Абсолютные и относительные ссылки
- •4. Функции
- •Суммесли(блок 1; условие; блок 2)
- •5. Организация справочников и работа с ними
- •6. Работа с электронной таблицей как с базой данных
- •6.1. Сортировка
- •6.2. Фильтрация
- •6.3. Функции баз данных
- •7. Визуализация данных
- •7.1. Построение диаграмм
- •7.2. Изменение используемого по умолчанию типа диаграммы
- •7.3. Работа с данными диаграмм
- •8. Задания для самостоятельной работы
- •9. Индивидуальный проект
- •Список использованных источников
- •Приложение Пример отчета по индивидуальному проекту
Суммесли(блок 1; условие; блок 2)
условие – условие, которому должно удовлетворять значение ячейки. Условие может быть записано в виде числа 154,6 или "154,6", текста "Иванов" или операции отношения с одним операндом – ">154,6";
блок 1 – блок ячеек, значения которых проверяются на выполнение условия;
блок 2 – блок ячеек, значения которых суммируются. Содержимое ячейки из блока 2 суммируется, если содержимое соответствующей ей ячейки блока 1 удовлетворяет условию. Если блок 2 не указан, то суммируются ячейки блока 1.
Задачи
При решении предлагаемых в качестве примеров задач, связанных с организацией электронных документов, требуется определить, какие формулы необходимо занести в соответствующие ячейки электронной таблицы. Формулы должны быть записаны таким образом, чтобы при их копировании правильные результаты получались без внесения в них изменений после копирования.
Желательно сначала записать формулы на бумаге и лишь потом проверить их правильность с помощью электронной таблицы.
Ответы к задачам приведены в конце пособия.
Задача 4.1
Электронный документ предназначен для расчета окладов работников различных разрядов с учетом вредности работ. Вручную в документ заносятся все тексты, номера разрядов и исходные данные для расчетов: оклады, соответствующие нулевым доплатам за вредность (колонка B), и размеры доплат за вредность в процентах (ячейки В2:E2). Исходные данные в процессе использования документа могут изменяться.
|
A |
B |
C |
D |
E |
|
|
1 |
Раз- |
Доплаты за вредность (%) |
|
||||
2 |
ряд |
0 |
4 |
8 |
14 |
|
|
3 |
1 |
500 |
520 |
540 |
570 |
|
|
4 |
2 |
550 |
572 |
594 |
627 |
|
|
5 |
3 |
600 |
624 |
648 |
684 |
|
|
6 |
4 |
650 |
676 |
702 |
741 |
|
|
7 |
5 |
700 |
728 |
756 |
798 |
|
|
8 |
6 |
800 |
832 |
864 |
912 |
|
Расчет оклада с учетом вредности выполняемых работ выполняется по следующему правилу: оклад, соответствующий данному разряду, увеличивается на указанное для данной колонки количество процентов. Кроме того, рассчитанное значение округляется до целого.
Какую формулу следует поместить в ячейку C3, чтобы после копирования ее в ячейки блока C3:E8 везде были получены правильные результаты?
Задача 4.2
Электронный документ предназначен для расчета остаточной стоимости оборудования, определяемой как начальная стоимость минус износ, рассчитываемый по следующему правилу:
износ = S*Y*P/100,
где Y – разность между текущим годом и годом выпуска оборудования;
P – процент износа за год;
S – начальная стоимость.
В случае, если износ превышает начальную стоимость, в качестве остаточной стоимости должен быть указан нуль.
|
A |
B |
C |
D |
E |
F |
G |
1 2 |
Наименование оборудования |
Год выпуска |
Начальная стоимость |
Остаточная стоимость |
|
Текущая дата |
Износ за год (%) |
3 |
Принтер |
1998 |
18000 |
7200 |
|
27.10.04 |
10 |
4 |
Клавиатура |
2004 |
345 |
345 |
|
|
|
…. |
. . . |
. . . |
. . . |
. . . |
|
|
|
Исходными данными для проведения расчетов являются годы выпуска оборудования (колонка B), начальная стоимость оборудования (колонка C), а также текущая дата и величина износа за год (ячейки F3, G3).
Какую формулу следует поместить в ячейку D3, чтобы после ее копирования в нижележащие ячейки этого столбца везде получались правильные результаты?
Задача 4.3
Электронный документ предназначен для определения года, которому соответствует наименьший объем выпуска каждого из указанных в документе видов продукции. В случае, если наименьший объем выпуска продукции фиксировался более одного года, в качестве результата должен быть указан последний год.
|
A |
B |
C |
D |
E |
1 |
Наименование |
1995 |
1997 |
1998 |
Год наименьшего |
2 |
продукции |
год |
год |
год |
выпуска продукции |
3 |
Продукт 1 |
120 |
118 |
118 |
1998 |
4 |
Продукт 2 |
113 |
128 |
119 |
1995 |
... |
. . . |
. . . |
. . . |
. . . |
. . . |
Исходными данными для проведения расчетов являются значения объемов выпуска каждого вида продукции (колонки B, C, D, начиная с третьей строки) в каждый год (ячейки B1:D1).
Какую формулу следует занести в ячейку E3, чтобы после копирования ее в нижележащие ячейки этого столбца везде получались правильные результаты?