III.3. Использование логической функции Если 5 страница

Таблица 1. Таблица учета торговых операций.

Номер	Дата продажи	Фирма	Код	Наименован. товара	Цена	Кол-во	Сумма	Скидка

Наименование товара и Цена должны вставляться автоматически в зависимости от кода товара из таблицы Каталог товаров.

Сумма просчитывается автоматически.

Рассчитайте скидку, используя функцию Если:

a) если сумма больше 1 000, но меньше 3 000, то скидка 1%;

b) если сумма больше 3 000, но меньше 10 000, то скидка 3%;

c) если сумма больше 10 000, то скидка 5%.

На третьем и четвертом листе постройте сводные таблицы Продажа товаров (количество) по фирмам и Сумма продаж фирмам по месяцам. Присвойте листам имена сводных таблиц.

Таблица 2. Продажа товаров (количество) по фирмам.

	Фирма
Наименован. товара	Фирма1	Фирма2	ФирмаN	Общий итог

Таблица 3. Сумма продаж фирмам по месяцам.

Дата продажи	Фирма	Общий итог
Фирма1	Фирма2	ФирмаN
Январь
Февраль
Март
И т.д.

На пятом и шестом листах постройте сводные диаграммы для сводных таблиц. Листы для построения диаграмм должен быть альбомной ориентации, все поля по 3 см. Диаграммы должны иметь наименование диаграммы, подписи и содержать легенду. Присвойте листам имена диаграмм.

На первом листе с помощью графических объектов и макрокоманд создайте содержание.

Технология работы:

1. С помощью панели инструментов рисование создайте графический объект.

2. Внутри графического объекта сделайте надпись Учет (по названию второго листа).

3. Запишите макрос. В меню Сервис выберите команду Макрос, Начать запись. В диалоговом окне Запись макроса назначьте имя макросу Учет. Нажмите кнопку Ок.

4. Откройте лист Учет и нажмите кнопку Остановить запись на панели инструментов Остановка записи.

5. Вернитесь на первый лист. Вызовите контекстное меню графического объекта Учет и выберите команду Назначить макрос.

6. В диалоговом окне Назначить макрос объекту выберите имя макроса Учет и нажмите кнопку Ок.

7. Аналогичным образом создайте графические объекты для остальных листов и назначьте им соответствующие макросы.

VII. ДОПОЛНИТЕЛЬНЫЕ ЗАДАНИЯ

Задача 1

В некоторых видах спортивных состязаний выступление каждого спортсмена независимо оценивается несколькими судьями, затем из всей совокупности оценок удаляются наиболее высокая и наиболее низкая, а для оставшихся оценок вычисляется среднее арифметическое, которое и идет в зачет спортсмену. Если наиболее высокую оценку выставили несколько судей, то из совокупности оценок удаляется только одна такая оценка; аналогично поступают с наиболее низкими оценками. Пусть в именованном диапазоне «Оценки» проставлены оценки, выставленные судьями одному из участников соревнований. Считая, что количество оценок заведомо не менее трех, определить оценку, которая пойдет в зачет.

Задача 2

Говорят, что матрица А имеет седловую точку а_у, если это число является минимальным в i-й строке и максимальным в j-м столбце. В блоке А1:Е10, содержащем числа, сосчитать количество Седловых точек.

Задача 3

Получить матрицу

1 2 4 8 … 128

2 1 2 4 … 64

4 2 1 2 … 32

… … … … … …

128 64 32 16 … 1

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

Задача 4

В блоке А1:А10 дана последовательность чисел. Вычислить в блоке В1:В9 разности соседних элементов исходного блока (так называемые первые разности, т.е. из второго элемента вычесть первый, из третьего второй и т.д.). Если все они положительные, выводить сообщение «Последовательность возрастающая», если все они отрицательные – «Последовательность убывающая».

Задача 5

Дан прямоугольный параллелепипед со сторонами a, b, c. Вычислить:

· объем V = abc ;

· площадь поверхности S = 2(аb + bс + ас );

· длину диагонали;

· угол между диагональю и плоскостью основания φ =

· угол между диагональю и боковым ребром α = π/2 – φ;

· объем шара, диаметром которого является диагональ,

Задача 6

В правильной треугольной пирамиде заданы: длина стороны

основания a и высота h. Вычислить:

· объем

· угол наклона бокового ребра к плоскости основания

· длину бокового ребра

· радиус описанного около пирамиды шара

· угол наклона боковой грани к основанию β =

· радиус вписанного в пирамиду шара ;

· площадь полной поверхности пирамиды S = 3V / r .

Задача 7

В правильной четырехугольной пирамиде заданы: длина стороны основания a и высота h. Вычислить:

· объем

· угол наклона бокового ребра к плоскости основания

α =

· длину бокового ребра

· радиус описанного около пирамиды шара

· угол наклона боковой грани к основанию β = arctg (2h/a)

· радиус вписанного в пирамиду шара r = a/2 tg(β/2)

· площадь полной поверхности пирамиды S = 3V / r .

Задача 8

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

Указание: достаточно сгенерировать номера от 0 до 999.

Задача 9

Транспортная компания получила следующий документ о категориях грузов и районах их доставки:

Категория груза 5 5 3 2 2 3

Район доставки U1 R2 R1 R2 Ltv U2

Категория груза 5 4 3 1 2 5

Район доставки U2 R3 R3 U1 U2 R3

Пусть этот документ находится в текстовом файле transport.txt (подготовьте этот файл самостоятельно, данные в каждой строке разделите пробелами). Постройте гистограммы числа грузов каждой категории и числа грузов в каждый район доставки.

VIII.ОСНОВНЫЕ КОНТРОЛЬНЫЕ ТЕМЫ ПО EXCEL

1. Структура интерфейса Excel. (Настройка экрана Excel для работы: панели инструментов, строка формул, линейки, строка состояния, вид документа, масштаб для просмотра и т.д. Установка параметров страницы: ориентация листа, размер бумаги, поля на странице, печать сетки. Создание колонтитулов).

2. Работа с листами.

3. Ввод данных в таблицу. Форматирование данных. Ввод в ячейку текста из нескольких строк.

4. Понятие абсолютного и относительного адреса и использование их в расчётах (на примерах).

5. Редактирование содержимого ячеек (исправление ошибок, ввод новой информации).

6. Найти и Заменить информацию в ячейках.

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

8. Перемещение и копирование информации из одного места таблицы в другое.

9. Изменение высоты и ширины ячейки.

10.Вставка и удаление ячеек, строк и столбцов, рабочих листов в книге.

11.Удаление содержимого ячейки, удаление форматов ячейки.

12.Копирование формата ячейки.

13.Автоформат таблицы.

14.Присваивание нескольким столбцам общего заголовка с учётом названия каждого из них.

15.Защита ячеек, листов и рабочей книги.

16.Фиксирование заголовков на странице.

17.Разбивка окна документа на части.

18.Копирование формул и копирование значений, полученных по формулам.

19.Использование математических и тригонометрических функций в расчётах (на примерах).

20.Использование статистических функций в расчётах (на примерах).

21.Режим автозаполнения для текста, чисел, дат и формул.

22.Создание пользовательских списков для автозаполнения.

23.Заполнение рядов данных с заданным шагом (арифметическая, геометрическая прогрессии). Заполнение дат с заданным шагом.

24.Создание и использование имён ячеек и рабочих листов.

25.Сортировка данных в таблице в алфавитном порядке или по убыванию (возрастанию), сортировка по первому ключу.

26.Фильтрация данных.

27.Сводная таблица.

28.Создание диаграммы на рабочем листе. Внедрённая диаграмма и диаграмма на отдельном листе. Типы диаграмм.

29.Редактирование диаграмм: вставка названий, меток данных, легенды, осей, сетки, новых данных и т.д.

30.Возможности форматирования: изменение типа диаграммы, тонирование диаграмм, наклейки, различные маркёры на графиках и т.д.

IX.ТЕСТОВЫЕ ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ ПО EXCEL

Задание № 1

Какой результат даст формула в ячейке С1

1) 1	2) ложь
3) 0	4) истина

Задание № 2

Представлен фрагмент электронной таблицы в режиме отображения формул.

Значение в ячейке В3 будет равно...

1) 1	2) 3
3) 5	4) 7

Задание № 3

Представлен фрагмент электронной таблицы в режиме отображения формул

Значение в ячейке В3 будет равно

1) 7	2) 5
3) 6	4) 10

Задание № 4

Для того чтобы формула = А1*В1, находящаяся в ячейке С1 листа Excel, ссылалась на значение А1 при копировании этой формулы в ячейку Н12, необходимо…

1) исправить формулу в С1 на =$A1*$B1