ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL (рабочая тетрадь, методические материалы)

ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL Бублик Е.Н. Электронные таблицы Excel. Оглавление §1. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ.........................................................................................................1 §2. ВВОД ФОРМУЛ................................................................................................................................................ 5 §3. ФОРМАТИРОВАНИЕ ТАБЛИЦЫ.................................................................................................................... 7  §4. УПРАВЛЕНИЕ ЛИСТАМИ РАБОЧЕЙ КНИГИ................................................................................................8 §5. ФУНКЦИИ EXCEL............................................................................................................................................ 9 ................................................................................................................................................................................. 9 §6. ПОСТРОЕНИЕ ДИАГРАММ И ГРАФИКОВ...................................................................................................9 §7. ЛОГИЧЕСКИЕ ФУНКЦИИ EXCEL.................................................................................................................11 §8. РАБОТА СО СПИСКАМИ EXCEL.................................................................................................................. 13 §9. ЗАДАЧИ ОПТИМИЗАЦИИ В EXCEL.............................................................................................................15 §10. ОБМЕН ДАННЫМИ МЕЖДУ EXCELИ ДРУГИМИ ПРИЛОЖЕНИЯМИ WINDOWS................................20 §1. ВВОД И РЕДАКТИРОВАНИЕ ДАННЫХ Электронные таблицы (ЭТ) служат для: • обработки числовой информации, представленнойв виде таблицы; • • сохранения таблиц на дисках; для вывода документа на печать. Возможности ЭТ: ввод и редактирование данных; вычисления по формулам; • • • форматирование таблиц; • • • • • →Выход. построение графиков и диаграмм; сортировка данных; работа как с базой данных; просмотр документа перед печатью; вывод на печать и пр. Запуск   электронных   таблиц:  Пуск  →Программы   →MicrosoftExcel.Выход   из   программы:  Файл Рабочее поле Excelразделено линиями по вертикали на столбцы (обозначаются латинскими буквами А, В, С, D, ...,AA, всего их 256), а по горизонтали на строки (обозначаются числами 1, 2, 3, ..., 65536). Пересечение строк и столбцов образует клетки, называемые  ячейками  таблицы. Каждая ячейка имеет свой адрес, состоящий из имени столбца и номера строки, например: ВЗ, $DA$108, F$57. Адреса бывают относительные (A3, F45, 117), абсолютные ($А$3, $F$45, $H$7 — фиксируются и столбец, и строка) и смешанные ($АЗ — фиксируется только столбец, А$3 — фиксируется только строка). Задание 1. Ознакомтесь с основными элементами окна электронной таблицы. F4 – клавиша для установки в строке формул абсолютного или смешанного адреса. Активная ячейка  выделяется жирным контуром. В активную ячейку осуществляется ввод данных через клавиатуру. Основной элемент ЭТ – ячейка. Для перехода к нужной ячейке можно использовать: клавиши управления курсором;   мышь;  поле имени. В любую ячейку можно ввести:  число; 1 текст;   формулу. Бублик Е.Н. Электронные таблицы Excel. В ячейку нельзя ввести рисунок, звук. Ввод данных в ячейку: 1) выбрать ячейку; 2) ввести данные (десятичные числа вводятся с разделителем запятая!); 3) нажать клавишу  Enter  (или любую клавишу управления курсором, или щелкнуть мышью в другой ячейке). Содержимое ячейки всегда отображается как в самой ячейке, так и в строке формул. 2 Бублик Е.Н. Электронные таблицы Excel. 3 Задание 2.Введите данные следующей таблицы: Бублик Е.Н. Электронные таблицы Excel. Редактирование данных; 1) выбрать нужную ячейку; 2) щелкнуть мышью в строке формул или дваждыщелкнуть левой кнопкой мыши внутри ячейки; 3) отредактировать содержимое ячейки; 4) нажать Enterили щелкнуть мышью в другойячейке. Изменение ширины столбца (высоты строки): 1        способ 1) подвести курсор мыши к границе столбца (строки), курсор примет вид ┤├; 2) передвигать границу до нужного размера, не отпуская левой кнопки мыши; 3) отпустить левую кнопку мыши. 2        способ 1) выделить нужный столбец (строку); 2) выбрать Формат, Столбец (Строка), Автоподбор ширины (автоподбор высоты). Задание 3. Подберите ширину столбцов так, чтобы были видны все надписи. При работе с ЭТ все действия выполняются над выделенным объектом. Выделение объектов Объект ячейка строка столбец Действия щелкнуть левой кнопкой мыши на ячейке щелкнуть левой кнопкой мыши по номеру строки щелкнуть левой кнопкой мышипо имени столбца группа ячеек протягивание мыши при нажатойлевой кнопке мыши группа отдельно  расположенных ячеек весь лист Ctrl + указать мышью нужные ячейки щелкнуть   левой   кнопкой   мыши   по   «пустому» прямоугольнику, находящемуся слева от первого столбца и сверху первой строки А1:СЗ — группа ячеек (А1,В1,С1, А2,В2,С2, АЗ,ВЗ, СЗ) Вставка строки (столбца): 1) выделить строку (столбец), перед (слева) которой нужно вставить новую строку (столбец); 2) выбрать Вставка, Строки (Столбцы). Задание 4. Вставьте новый столбец перед столбцом А. В ячейку А1 введите текст № п/п, пронумеруйте ячейки А2:А7, используя автозаполнение, для этого в ячейку А2 введите 1, в ячейку  A3 введите  2, выделите эти ячейки, потяните за маркер Автозаполнениявниз до строки 7. 4 Бублик Е.Н. Электронные таблицы Excel. Задание   5.   Вставьте   строку   для   названия   таблицы.   В   ячейку  А1  введите   название   таблицы Индивидуальные вклады коммерческого банка. Удаление строки (столбца): 1) выделить строку (столбец); 2) выбрать Правка, Удалить. Копирование содержимого ячеек: 1        способ 1) выделить ячейку (группу ячеек); 2) выбрать кнопку Копировать; 3) указать нужную ячейку (группу ячеек); 4) выбрать кнопку Вставить. 2        способ 1) установить   указатель   мыши   на   правый   нижнийугол   копируемой   ячейки   (курсор   примет формучерного   крестика)   и,   держа   нажатой   кнопкумыши,   протянуть   курсор   на   всю   область копирования; 2) снять выделение. Удаление содержимого ячеек: 1) выделить ячейку (группу ячеек); 2) нажать Deleteили выбрать кнопку Вырезать. Сохранение (открытие) файла: 1) выбрать в строке меню пункт Файл 2) выбрать Сохранить как (Открыть); 3) выбрать в поле Папка имя диска; 4) выбрать имя нужного каталога; 5) в поле  Имя  файла  щелкнуть мышью  инабратьна  клавиатуре  имя  своего файла  (выбрать изсписка имя файла); 6) выбрать Сохранить (Открыть). Файлы, созданные в электронных таблицах Excel, имеют расширение xls. Задание 6. Сохраните таблицу на дискете под именем банк.xls. §2. ВВОД ФОРМУЛ Формула — это выражение, состоящее из операндов, соединенных знаками операций. Формула должна начинаться со знака = (равно). 5 Выражения, входящие в формулу, могут быть  арифметическими, логическими  и  строковыми Бублик Е.Н. Электронные таблицы Excel. (текстовыми строками). Операндами могут быть: числа; текстовые константы; адреса ячеек (ссылки); • • • • функции (математические, финансовые, функции времени идаты, статистические и др.); • Пример формулы: =2*А34+8^2.3/СУММ(В2:В23) При вводе формулы адреса ячеек указываются мышью или вводятся с клавиатуры на выражения в круглых скобках (арифметические,логические или строковые). английском языке! При копировании формулы: • относительные ссылки изменяются; • абсолютные ссылки не изменяются; • смешанные ссылки изменяются частично. В арифметических выражениях используются знаки арифметических операций: В логических выражениях используются знаки операций сравнения: +   сложение ­   вычитание *  умножение =   равно < меньше >больше / деление" ^   возведение в степень %  процент <=   меньше или равно >=   больше или равно <>не равно Для текстовых данных используется оператор строки & (сцепление строк). Если текстовая строка является операндом в выражении, то она должна быть заключена в двойные кавычки, например: "первая строка". Помните, при работе с формулами в ячейке мы видимрезультат вычисления по формуле, а в строке формул(для выделенной ячейки) — саму формулу. Сообщения об ошибках #### #ДЕЛ/0! #ЗНАЧ! #ИМЯ? #н/д #ПУСТО! #ССЫЛКА! размер   ячейки   недостаточен   для   размещения числа или результата деление на ноль недопустимый тип аргумента или операнда неверное имя функции или области неопределенные данные задано   пересечение   двух   областей,   не имеющих общих ячеек недопустимая ссылка на ячейку #ЧИСЛО! ошибка в вычислениях Важнейшее свойство ЭТ: При изменении числовых данных пересчет в таблице происходит автоматически! Задание 7. Откройте файл банк.xls. 1) В ячейку  С9  введите формулу для нахождения общей суммы, для этого выделите ячейку  С9, нажмите кнопку ΣАвтосуммирование, выделите группу ячеек СЗ:С8, затем нажмите Enter. 2) В     ячейкуD3  введите  формулудля   нахождениядоли   от   общего   вклада,   используя абсолютнуюссылку на ячейку С9: =СЗ/$С$9*100. 3) 2) Скопируйте данную формулу для группы ячеекD4:D8 любым способом. Сохраните измененияв файле. 6 §3. ФОРМАТИРОВАНИЕ ТАБЛИЦЫ Бублик Е.Н. Электронные таблицы Excel. Задание 8.  Ознакомьтесь с   название кнопок форматирования (используйте подсказки па экране монитора). Формат ячеек: 1) 2) 3) 4) 5) выделить ячейку (группу ячеек); выбрать Формат, Ячейки; выбрать нужный ярлык; выбрать нужную категорию; нажать ОК. При работе с таблицами удобно использовать команды  контекстного меню  (вызываются нажатием правой кнопки мыши). Простая сортировка данных: 1) выделить диапазон ячеек; 2) выбрать тип сортировки (по возрастанию илиубыванию), используя кнопки Сортировка по нескольким полям (ключам): 1) выделить диапазон ячеек; 2) выбрать Данные, Сортировка; 3) указать необходимые параметры сортировки; 4) нажать ОК. Задание 9. Откройте файл банк.xls. 1) Для группы ячеек  СЗ:С9  установите  Разделитель тысяч и разрядность Две цифры после запятой,используя следующие кнопки 2) Для группы ячеек D3:D8 установите разрядностьЦелое число. 2) Объедините ячейки А1:D1. 3) Для   ячеек  A2:D2  установите  Формат,   Ячейки,Выравнивание,  предварительно   уменьшив размерыполей и увеличив высоту 4­й строки. 5) Выполните сортировку по убыванию суммы вклада. 6) Добавьте   две  строки   после   названия   таблицы.   Введите   в   ячейку  А2  текст  Дата,  в  ячейку  В2  — сегодняшнюю   дату  (например,  .25.10.2003),в   ячейкуA3  текст  Время,    в   ячейку  ВЗ     —    текущее 7 время(например,  15:08).  Выберите  формат даты и времени в соответствующих  ячейках по своему желанию. 7) Выполните форматирование таблицы по образцув конце задания. Бублик Е.Н. Электронные таблицы Excel. 8) Снимите сетку. 9) Сохраните документ под тем же именем. §4. УПРАВЛЕНИЕ ЛИСТАМИ РАБОЧЕЙ КНИГИ Новая   книга Excelсодержит три рабочих листа с именами Лист1, Лист2, ЛистЗ. рабочая   Листы   рабочей   книги   можно переименовывать, перемещать, копировать,   удалять,   добавлять новые.   Переименование листа: 1) выбрать нужный лист; 2) дважды   щелкнуть   левой кнопкой мыши по ярлыку; 3) написать новое название листа (можно на русском языке). Вставка листа: 1) выбрать лист, слева от которого нужно вставить новый лист; 2) выбрать Вставка, Лист или в контекстномменю Добавить. Удаление листа: 1) выбрать нужный лист; 2) выбрать Правка, Удалить лист или в контекстном меню Удалить. Связывание рабочих листов В   формулах   можно   ссылаться   не   только   на   данные   в   пределах   одного   листа,   но   и   на   данные, расположенные в ячейках других листов данной рабочей книги и даже в другой рабочей книге. Ссылка на ячейку другого листа состоит из имени листа и имени ячейки (между именами ставится восклицательный знак !), например, Лucm2!F3. Задание 10. Выполните задание: 1) Откройте новую рабочую книгу. 2) Переименуйте   листы   рабочей   книги:   вместо  Лист1введите  Налог   с   продаж,    вместоЛист2 введитеРасчет цен. 3) Удалите ЛистЗ. 4) На   листе  Налог   с   продаж  введите   в   ячейки  В1  и  С1соответственно   текст  Налог   с   продаж  и числовоезначение 0,05. 5) Перейдите на лист Расчет цеп. 6) Заполните его исходными данными. Заполнитепустые ячейки. 7) Оформите   таблицу,   выполните   сортировку   товарапо   возрастанию,   сохраните   документ   под именемофис.xls. 8 §5. ФУНКЦИИ EXCEL Бублик Е.Н. Электронные таблицы Excel. Excelсодержит более 400 встроенных функций для выполнения стандартных вычислений. Ввод функции начинается со знака = (равно). После имени функции в круглых скобках указывается список аргументов, разделенных точкой с запятой. • • • • Аргументами функции могут быть: числа; текст; адреса ячеек; выражения, содержащие другие функции. Некоторые функции могут иметь необязательные аргументы, которые можно опускать. Часто используемые функции СУММ(В2:В5) СУММ(В2:В5;100;К4) СУММЕСЛИ(В2:В5;">10") СРЗНАЧ(В2:В5) МАКС(В2:В5) МИН(В2:В5) СЧЕТ(В2:В5) СЧЕТЕСЛИ(В2:В5;"<5") вычисление суммы числовых значений диапазона ячеек В2:В5 вычисление   суммы   числовых   значений   диапазона   ячеек  В2:В5, числа 100 и значения ячейки К4 вычисление суммы чисел,больших 10, из диапазона ячеек В2:В5 вычисление среднего значения для диапазона ячеек В2:В5 вычисление максимального значения из диапазона ячеек В2:В5 вычисление минимального значения из диапазона ячеек В2.В5 подсчет общего количества чисел из диапазона ячеек В2:В5 вычисление количества чисел, меньших 5, из диапазона ячеек  В2:В5 fx   — мастер функций (используется для вызова встроенной функции) Задание 11.  Дана последовательность чисел: 25; ­61; 0; ­82; 18; ­11; 0; 30; 15; ­31; 0; ­58; 22. В ячейку  А1  введите текущую дату, используя мастер функций (категория функции  Дата  иВремя). Числа вводите в ячейки  третьейстроки. Заполните ячейки  К5:К14  соответствующими формулами. Отформатируйте   таблицу   по   образцу.  Лист1переименуйте   в  Числа,  остальные   листы   удалите. Результат сохраните под именем числа.xls. §6. ПОСТРОЕНИЕ ДИАГРАММ И ГРАФИКОВ Диаграмма (график)  — графическое   числовых наглядное   представление данных. Диаграммы предназначены   для   сравнения нескольких величин или нескольких значений одной величины и слежения за изменением их значений и т.п. Типы диаграмм:  гистограмма (столбчатая диаграмма) —  для сравнения нескольких величин в нескольких точках круговая — для сравнения нескольких величин в одной точке график —  для слежения за изменением нескольких величин при переходе от одной точки к другой   Основные элементы диаграммы:    название диаграммы; легенда; подписи осей. Этапы построения диаграммы: 9 1) выделить ячейки, по которым будет строитьсядиаграмма; Бублик Е.Н. Электронные таблицы Excel. 2) нажать кнопку Мастер диаграмм; 3) Шаг   1   —   выбрать   нужный   тип   и   вид   диаграммы   (результат   можно   просмотреть, удерживаянажатой кнопку Просмотр результатов), нажать кнопку Далее; Шаг 2 — посмотреть результат, если необходимо — внести изменения в Диапазоне данных или Рядах данных; Шаг 3  — указать  параметры  диаграммы  (название  диаграммы,  подписи  осей,   расположение легенды и т.д.); Шаг 4 — указать размещение диаграммы, выбрать Готово. Изменение размеров диаграммы: 1) выделить диаграмму мышью; 2) потянуть за любой квадратный маркер; 3) снять выделение. Редактирование диаграммы: 1) выделить диаграмму; 1) двойным щелчком мыши выделить нужный элемент диаграммы; 2) внести необходимые изменения и нажать ОК. Задание 12. Выполните задание. 1) Откройте документ бaнк.xls. 2) Переименуйте Лист1 в Таблица, Лист2 в Диаграмма, удалите лишние листы. 3) На листе  Диаграмма  постройте  круговую  диаграмму  по данным  столбцов  Фамилия  вкладчика, Доляот общего вклада. 4) Вырежьте кусочки из диаграммы, для этого: • • щелкните внутри любого сектора; выделите область диаграммы; • удерживая нажатой левую кнопку мыши, перетащите сектор в сторону на 1 см. 5) Подберите   подходящий   размер   шрифта   подписейданных,   цвет   шрифта установите соответственноцвету каждого сектора. 6) Для названия диаграммы установите шрифтCourier, 12 пт, синий цвет. 7) Переместите легенду в правый нижний угол окнадиаграммы. 8) Сохраните документ под тем же именем. Задание 13. Выполните задание. 1) Откройте документ офис.хls. 2) Вставьте новый лист, переименуйте его в Диаграмма. 3) По данным столбцов Наименование товара и Сумма постройте гистограмму. 4) Укажите   над   каждым   столбцомчисловые   значения,   подобравнеобходимый размер шрифта. 5) Сделайте заливку рамки невидимой. 6) Остальные элементы диаграммыотформатируйте по своему желанию. 1) 3)   2) Сохраните документ   под   тем жеименем. Задание 14. Постройте   график функции у = sinx на отрезке   [­5;   5]   с шагом 0,5. Используйте   тип диаграммы Точечная.   10 Сохраните документ под именем график.xls Бублик Е.Н. Электронные таблицы Excel. 15. Выполните задание «Изменение биоритмов человека». 1) На Лucme1создайте таблицу по образцу. 2) Заполните исходные данные: А1 — фамилия, имя исследуемого  А2 — дата рождения (число, месяц, год)  A3 — текущая дата А4 — формула для расчета прожитых дней (с разделителем тысяч)  А7 — текущая дата (число, месяц)  А8, А9 ит.д. — последующие даты (число, месяц)  В7, В8 и т.д. — количество прожитых дней 3) Вычислите биоритмы физической, эмоциональнойиинтеллектуальной активностипо формуле: 2π(t−t0) F=sin T , где t — время (дата) t0 — дата рождения Т —  соответствующий период активности (23, 28или 33 дня). 4) Отформатируйте таблицу по своему желанию. 5) Создайте   колонтитулы  (Вид→Колонтитулы):  в   верхнем   укажите   фамилию   и   имя   (по   центру),в нижнем — дату и время выполнения работы (справа). 6) Переименуйте Лucm1 в Таблицу. 7) На Листе2 постройте график изменения вашихбиоритмов. Установите минимальный размер шрифта для подписей. 8) Проанализируйте   полученные   результаты.   По   графику   определите,   в   какие   дни   ваша физическая,эмоциональная  и интеллектуальная  активность  достигает  максимумов  и  минимумов  (в отдельности).Есть   ли   критические   дни   (совпадение   значенийвсех   функций)?   Укажите   их   в произвольной формепод графиком. 9) Задайте альбомную ориентацию данного листа. 10) Переименуйте Лист2 в Диаграмму. 11) Удалите лишние листы. 12) Сохраните данные под именем мои биоритмы.хls. §7. ЛОГИЧЕСКИЕ ФУНКЦИИ EXCEL Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Функция ЕСЛИ позволяет определить, выполняется ли указанное условие. Если условие истинно, то значением ячейки будет выражение 1, в противном случае — выражение2. =ЕСЛИ(условие; выражение1; выражение2) Например, =ЕСЛИ(В2>20; "тепло"; "холодно") Если значение в ячейке В2 >20, то выводится сооб­ щение тепло, в противном случае — холодно. Совместно с функцией ЕСЛИ используются логические операции И, ИЛИ, НЕ. Например, =ЕСЛИ(И(Е4<3; Н$98>=13); "выиграет"; "проиграет") Если значение в ячейке Е4< 3 и Н$98>=13, то выводится сообщение выиграет, в противном случае — проиграет. 11 Задание 16. Выполните задание.  Бублик Е.Н. Электронные таблицы Excel. 1) Заполните таблицу иотформатируйте ее по образцу. 2) Заполните формулами пустые ячейки. Абитуриентзачислен  в институт, если сумма баллов больше илиравна проходному баллуиоценка по математике4 или 5, в противном случае — нет. 3) Выполните сортировку по убыванию общей суммыбаллов. 3) Сохраните документ под именем студент.xls. Задание 17. Постройте таблицу истинности для логической операции ИЛИ. Указание:  логическая сумма истинна (1) тогда и только тогда, когда хотя бы одно высказывание истинно (1). Выполните форматирование таблицы по образцу. Результат сохраните под именем логика17.xls. Задание   18.  Постройте   таблицу   истинности   для   высказывания  F=А   +   В   ∙● ´С .   Выполните форматирование таблицы. Сохраните результат под именем логика 18.xls. Задание 19. Выполните задание«Обработка данных метеостанции». 1) Заполните таблицы. 12 2) Заполните   формулами пустые  ячейки.  Засушливыммесяцем   считать  месяц,  в котором количество выпавших осадков меньше 15 мм (воспользуйтесьформулой СЧЕТЕСЛИ). Бублик Е.Н. Электронные таблицы Excel. 3) Заполните столбец Прогноз: засуха, если количество осадков < 15 мм; дождливо, если количество осадков > 70 мм; нормально (в остальных случаях). • • • 4) Представьте   данные   таблицы  Количество   осадков   (мм)  графически,   расположив   диаграмму   на Листе2. Выберите тип диаграммы и элементыоформления по своему усмотрению. 5) Переименуйте Лист1 в Метео, Лист2 в Диаграмма. Удалите лишние листы рабочей книги. 6) Подготовьте документ к печати: • выберите ландшафтную ориентацию страницы; • подберите ширину полей так, чтобы все три таблицы умещались на странице; • уберите сетку; • укажите в верхнем колонтитуле Вид, Колонтитулы (Сверху ­ свою фамилию, а в нижнем — дату выполнения работы). 7) Сохраните таблицу под именем метео.xls. §8. РАБОТА СО СПИСКАМИ EXCEL Автоматизация ввода данных Облегчить и ускорить ввод данных позволяет режим Авто заполнения (работает с числами, датами, днями недели, месяцами и смешанными данными). В  Excel  существуют стандартные текстовые ряды — списки, содержащие названия дней недели и названия месяцев. Стандартные списки чаще всего используются для заголовков столбцов и строк. Для просмотра существующих списков выполните Сервис, Параметры, Списки. Для   создания   нового   списка   выполните  Сервис,   Параметры,   Списки,   Новый   список.  Укажите элементы списка, разделяя элементы списка нажатием клавиши Enter. Нажмите Добавить. Автоматизированный ввод данных: 1) в первую ячейку диапазона ввести значение одного из элементов списка; 2) протащить   маркер   заполнения,   выделяя   диапазон   (если   выделенный   диапазон   больше количества элементов в списке, то он будет заполняться циклически). Задание 20. Выполните задание. 1) Заполните таблицу данными, для названия месяцев используйте Автозаполнение. 2) Добавьте столбец Всего. 3) Определите содержимое пустых ячеек. 4) Выполните сортировку в столбце В среднем по убыванию. 5) Оформите таблицу. 6) Переименуйте Лист1 в Магазин, Лист2 в Диаграмма, ЛистЗ удалите. 7) Скройте столбец Н, для этого: • выделите данный столбец или диапазон ячеекН2:Н6; выберите Формат, Столбец, Скрыть, Показать скрытый столбец: выделить смежные столбцы или смежные ячейки; выбрать Формат, Столбец, Отобразить. • • • 8) Добавьте колонтитулы (в верхний поместите фамилию и имя, в нижний — текущую дату и время). Посмотрите полученный результат. 9) Постройте круговую диаграмму, показывающую долю каждой статьи дохода по итогам I полугодия. Поместите диаграмму на соответствующем листе. 10) Сохраните результаты под именем магазин.xls. Режим фильтрация данных (работа в режиме Базы данных) 13 Фильтрацияпозволяет находить и отбирать для обработки часть записей (строк), которые содержат определенные значения или отвечают определенным критериям (условиям). Остальные строки при этом скрыты. Бублик Е.Н. Электронные таблицы Excel. Для   отбора   данных   используют  Автофильтр   (Данные,   Фильтр,   Автофильтр)  и  Расширенный фильтр   (Данные,   Фильтр,   Расширенный   фильтр).  Заголовки   столбцов   преобразуются   в раскрывающиеся   списки  (кнопки­стрелки),   в   которых   можно   задавать   нужные   критерии   для   поиска данных. В раскрывающемся списке выводятся все значения, встречающиеся в столбце, и дополнительные опции: Все, Первые 10, Условие, Пустые или Непустые. Опция Условие позволяет указать для одного столбца один или два критерия отбора, объединив их (И — если оба условия должны выполняться одновременно, ИЛИ — если выполняется хотя бы одно условие). В условиях поиска для текстовых полей можно задавать символы шаблона: * — для указания любой последовательности символов;  ? — для представления любого одного символа. Сброс  одного   из   фильтров   осуществляется   выбором   опции  Всераскрывающегося   списка   этого фильтра. Отмена режима фильтрации (без уничтожения фильтров) реализуется командой Данные, Фильтр, Показать все. Удаление  фильтров  (т.е.  отключение  Автофильтра)  производится  командой  Данные,  Фильтр, Автофильтр. Задание 21. «Расчет зарплаты». 1) Переименуйте рабочий Лucm1 в Зарплата, Лист2в Сортировка, удалите ЛистЗ. 2) Введите заголовки столбцов. 3) Для ввода данных в таблицу воспользуйтесь Формой, для этого: • • выделите любую ячейку заголовка таблицы; выберите Данные, Форма; • • внесите данные о каждом сотруднике в соответствующие «окошки», нажмите Добавить; после ввода последней записи нажмите кнопкуЗакрыть. 4) Заполните пустыеячейки (удержание составляет14%). 5) Для   данных   столбцов  Начислено,   Удержано,   К   выдаче  примените   денежный   формат   с разделителемтысяч. 6) Оформите таблицу по своему желанию. 7) Найдите,   используя  Автофильтр,  записи   о   сотрудниках   с   зарплатой   ниже   4000   р.   Скопируйте результаты поиска и поместите их под основной таблицей. 8) Найдите записи о сотрудниках 1­го отдела. Скопируйте результаты поиска и поместите их после результатов выполнения пункта 7. 9) Найдите записи о сотрудниках 2­го отдела, у которых сумма к выдаче находится в пределах от2000 р. до 4000 р. Скопируйте результаты поискаи поместите их после результатов выполненияпункта 8. 10) Найдите   все   записи   об   инженерах,   фамилии   которых   начинаются   па   букву   «П».   Скопируйте результаты поиска и поместите их после результатов выполнения пункта 9. В пунктах 11 — 13 скопируйте полученные результаты сортировки на лист Сортировка. Оставляйте между таблицами 2 строки. 11) Отсортируйте данные таблицы по двум ключам:Отдел (по возрастанию), Ф.И.О. (по возрастанию). 14 12) Отсортируйте данные по двум ключам: Отдел, Начислено (по убыванию). Бублик Е.Н. Электронные таблицы Excel. 11) Отсортируйте данные по трем ключам: а) Отдел, Должность, Ф.И.О.; б) Отдел, Должность, Таб. Номер. 14) Скройте на листе Зарплата содержимое столбцовС, D, E, F, G. 15) Сохраните документ под именем расчет зарплаты.хls. Задание 22. Выполните задание. 1) Заполните таблицу. Склад Заполните пустые ячейки с учетом формата данных(на некоторые продукты специально укажите просроченную дату реализации). 3) Переименуйте Лист 1в Склад, Лист2 — в Сортировка, ЛистЗ — в Поиск. 4) Выполните форматирование и обрамление таблицыпо образцу. 5) Отсортируйте   записи   в   порядке   убывания  Срокареализации.  Скопируйте   результат   на   лист Сортировка. 6) Отсортируйте данные по двум ключам: Продукты(по возрастанию), Цена (по убыванию). Скопируйте результат на лист Сортировка. 7) Найдите все продукты с истекшим сроком реализации. Скопируйте результат на лист Поиск. 8) Найдите   все   продукты,   для   которых   общая   суммане   меньше   300   р.   и   не   больше   1000   р. Скопируйтерезультат на лист Поиск. 9) Найдите   все   продукты,   количество   которых   больше300,   а   срок   реализации   еще   не   истек. Скопируйтерезультат на лист Поиск. 10) Придумайте свои критерии поиска (не менее двух). Запишите эти условия и скопируйте результат поиска на лист Поиск. 11) На всех листах в верхнем колонтитуле укажитесвою фамилию и дату выполнения работы, в нижнем — названия листов. 11) Сохраните документ под именем склад.xls. §9. ЗАДАЧИ ОПТИМИЗАЦИИ В EXCEL Задача оптимизации — поиск оптимального (наилучшего) решения данной задачи при соблюдении некоторых условий. При   решении   задач   оптимизации   на   компьютере   целесообразно   руководствоваться   следующим алгоритмом: 1) разобрать условие задачи; 2) построить математическую модель; 3) выбрать поисковые переменные; 4) 5) выбрать критерий оптимизации; 6) решить задачу на компьютере; 7) проанализировать полученные результаты. задать ограничения; Задача «Покраска пола» Вычислить количество краски для покрытия пола в спортивном зале. Сначала измеряют длину а (18,1 <= а < =18,3) и ширину b(7,6 <= b< =7,7) пола. Реальный объект — пол зала — заменяют прямоугольником, для которого S = аb. При   покупке   краски   выясняют,   какую   площадь  Sможно   покрыть   содержимым   одной   банки 15 Бублик Е.Н. Электронные таблицы Excel. (предположим меньше 10 м2), вычисляют необходимое количество банок  n=ab . S1 a, b, S1— поисковые переменные, значения которых можно изменять. Необходимо задать ограничения: а ≥18,1; а ≤18,3; b≤ 7,6; b≥7,7; S1≤10. Критерий оптимизации: количество банок должнобыть минимальным, т.е.  n=ab S1 =min. Решение на компьютере: 1) Заполнить таблицу, указав произвольные значения для поисковых переменных. 2) Найти оптимальное решение, для этого: • • Доступ к инструменту  Поиск решения осуществляется с помощью команды ­ Анализ  Данные выделить целевую ячейку В7; • Поиск решения. Если команда  Поиск решения  или группа  Анализ  отсутствует на вкладке  Данные, то необходимо загрузить соответствующую надстройку:  1. Выбрать команду Файл  Параметры. 2. В диалоговом окне Параметры Ехсеl выбрать категорию Надстройки 3. В поле Управление выбрать значение Надстройки Excel, затем кнопку Перейти. 4. В поле  Доступные надстройки  установить флажок рядом с пунктом  Поиск решения  и нажать   кнопку   ОК.   После   выполнения   этих   действий   команда  Поиск   решения  будет доступной в группе команд Анализ вкладки Данные 16 Бублик Е.Н. Электронные таблицы Excel. • • • • • • установить целевую ячейку, равную минимальному значению; указать мышью диапазон изменяемых ячеек; выбрать кнопку Добавитьдля записи ограничений; после записи ограничения нажать Добавить (дляпоследнего ограничения — ОК); нажать кнопку Выполнить; выбрать Тип отчета, Результаты и нажать ОК. На новом листе Отчет по результатам можно увидеть: В   электронных   таблицах  найдено   оптимальное   решение:  для   покраски   пола   в   актовом   зале необходимо не более 14 банок краски. Задание 23.  На научный семинар собрались ученые и обменялись визитными карточками. Число визитных карточек составило 210 штук. Сколько ученых приехало на семинар, если их было не более 20? Решение: х — количество ученых; п — количество карточек. Математическая модель: _____________________________________________________________________ ________________________________________________________________________________________ _________________________________________________________________________________________ Поисковые переменные: ______________________________________________________________________ Ограничения: ______________________________________________________________________________ ___________________________________________________________________________________________ _________________________________________________________________________________________ Критерий оптимизации: _________________________________________________________________ Найдите поиск решения в Excel, создайте отчет и сохраните документ под именем семинар, xls. Задание 24. Какие размеры должен иметь бак объемом V = abh= 2000 куб. см, чтобы на его изготовление пошло как можно меньше материала? Сторонаа должна быть не менее 10 см. Решение:  Математическая модель: ________________________________________________________________ ___________________________________________________________________________________________ Поисковые переменные:_________________________________________________________________ 17 Бублик Е.Н. Электронные таблицы Excel. Ограничения: _________________________________________________________________________ ______________________________________________________________________________________ Критерий оптимизации: _________________________________________________________________ Выполните поиск решения, заполнив таблицу: Создайте отчет и сохраните документ под именем бак. xls. Задание 25. На участке работает 20 человек; каждый из них в среднем работает 1800 ч в год. Выделенные ресурсы: 32 т металла, 54 тыс. кВт∙ч электроэнергии. План реализации: не менее 2 тыс. изделий Аи не менее 3 тыс. изделий Б. На выпуск 1 тыс. изделий Азатрачивается 3 т металла, 3 тыс. кВт ∙ ч элек­ троэнергии и 3 тыс. ч рабочего времени. На выпуск 1 тыс. изделий Б затрачивается 1 т металла, 6 тыс. кВт∙ч электроэнергии и 3 тыс. ч рабочего времени. От реализации 1 тыс. изделий  Азавод получает прибыль 500 тыс. р., от реализации 1 тыс. изделий Б — 700 тыс. р. Выпуск какого количества изделий Аи Б (в тыс. штук) надо запланировать, чтобы прибыль от их реализации была наибольшей? Составьте модель и решите задачу. Выполните Поиск решения, заполнив таблицу: Создайте отчет и сохраните документ под именем paбoma25.xls. Задание 26.  Кооператив из 20 человек выпускает  изделия  Л  и  Б  (см. Задание  25).  Кооператив намерен получать прибыль не менее 6,5 млн. р. в год. Ему выделили 54 тыс. кВт∙ч электроэнергии. Какое минимальное количество металла потребуется кооперативу,чтобы обеспечить нужную прибыль? Составьте модель и решите задачу. Решение: _____________________________________________________________________________ ______________________________________________________________________________________ ______________________________________________________________________________________ ______________________________________________________________________________________ Создайте отчет и сохраните документ под именем paбoma26.xls. Задание 27. Начальник участка изучает возможность расширить ассортимент товаров — добавить к 18 Бублик Е.Н. Электронные таблицы Excel. выпускаемым изделиям А и Б еще два вида изделий В и Г. Предварительное изучение спроса показало, что можно реализовать не более 5 тыс. изделий  В,  получив при этом прибыль в размере 1200 р. Скаждого изделия. Можно также реализовать не более 4 тыс. изделий Г, получив прибыль 1000 р. с изделия. На 1 тыс. изделий В расход металла составляет 0,5 т, электроэнергии 4 тыс. кВт∙ч, рабочего времени 5 тыс. ч. Для выпуска 1 тыс. изделий Г требуется 1,5 т металла, 4 тыс. кВт∙ч электроэнергии, 6 тыс. ч рабочего времени. Расширение ассортимента изделий потребует приобретение дополнительного оборудования на сумму 800 тыс. р., которая будет возмещена из прибыли.  Целесообразно лирасширение ассортимента выпускаемых товаров (можно ли спланировать выпуск товаров  А, Б, В, Г так, чтобы получить прибыль большую, чем при выпуске только товаров А и Б)? Решение:____________________________________________________________________________ _____________________________________________________________________________________ _____________________________________________________________________________________ _____________________________________________________________________________________ _____________________________________________________________________________________ Выполните поиск решения, создайте отчет и сохраните документ под именем paбoma27.xls. Задание   28.  Заведующий   хозрасчетной   больницей   должен   составить   штатное   расписание,   т.е. определить, сколько сотрудников, на какие должности и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет 10000 у.е. Известно, что для нормальной работы больницы нужно 5 — 7 санитарок ,8—10 медсестер, 10—12 врачей, 1 зав. Аптекой, 3 зав. Отделениями, 1 главный врач, 1 завхоз, 1 зав. Больницей. За основу берется оклад санитарки, а все остальные вычисляются по формуле:  А∙С + В,  где С — оклад санитарки,  Аи  В —  коэффициенты, которые для каждой должности определяются решением совета трудового коллектива. Допустим, совет решил, чтомедсестра должна получать в 1,5 раза больше санитарки (А = 1,5; В = 0); врач — в 3 раза больше санитарки; зав. отделением — на 30 у.е. больше, чем врач; зав. аптекой — в 2 раза больше санитарки; завхоз — на 40 у.е. больше медсестры; главный врач — в 4 раза больше санитарки; зав. больницей — на 20 у.е. больше главного врача. Составьте модель и решите задачу.  1) Заполните   таблицу,   установив   зарплату   санитарки   150   у.е.   Расположите   таблицу   на   листе Расписание. 3) Составьте   штатное   расписание   с   использованием   функции   автоматизации   расчетов  Подбор параметра (Сервис, Подбор параметра). 3) Составьте несколько вариантов штатного расписания, изменяя количество сотрудников на должностях   Подберите   зарплату   санитарки   в   новых   условиях.   медсестры, санитарки, Расположитетаблицу налисте Варианты.   врача. 4) Удалите остальные листы. 5) Сохраните документ под именем госпиталь.xls. 19 Бублик Е.Н. Электронные таблицы Excel. §10. ОБМЕН ДАННЫМИ МЕЖДУ EXCELИ ДРУГИМИ ПРИЛОЖЕНИЯМИ WINDOWS данными между разными приложениями Windows осуществляется с помощью буфера обмена. Задание 29. Выполните задание. Обмен 1) Откройте файл бак.xls. 2) Вставьте несколько новых строк перед таблицей. 3) Сверните окно Excel. 4) Запустите графический редактор Paint. 1) Создайте в нем рисунок бака с указанием ребера,b, h. 2) Вырежьте рисунок и поместите в буфер обмена (Правка, Копировать). 7) Перейдите в окно Excel и вставьте рисунок (Правка, Вставить). Поместите его перед таблицей. Добавьте или удалите лишние строки в зависимости отразмеров рисунка. 8) Сохраните документ под тем же именем. 9) Выделите ячейки с данными и поместите их в буфер обмена. 10) Закройте окно Excel. 11) Запустите текстовый редактор Word. 12) Наберите в нем следующий текст:  Результаты решения задачи оптимизации.  Работу выполнил(а) ученик (ученица) 11 класса «»  (укажите Ф.И.О.). Отформатируйте его по своему желанию. 13) Вставьте после текста данные из буфера обмена. 14) Сохраните документ под именем бак.doc. 20