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