- •Тема 7. Технология решения задач в среде табличного процессора
- •7.1 Методические указания и основные термины
- •7.1.1 Функциональные возможности и пользовательский интерфейс программы Excel
- •Упражнение 7.1
- •Упражнение 7.2
- •7.1.2. Создание и редактирование электронных таблиц
- •7.1.3. Форматирование электронных таблиц и операции над документами
- •Упражнение 7.3
- •Упражнение 7.4
- •Экзаменационная ведомость
- •Упражнение 7.5
- •Упражнение 7.6
- •7.1. 4. Построение, редактирование и форматирование диаграмм
- •Упражнение 7.7
- •Упражнение 7.8
- •Упражнение 7.9
- •Упражнение 7.10
- •Упражнение 7.11
- •Упражнение 7.12
- •Упражнение 7.13
- •Упражнение 7.14
- •Упражнение 7.15
- •Упражнение 7.16
- •7.1.5. Использование встроенных функций
- •Упражнение 7.16
- •Упражнение 7.17
- •Упражнение 7.18
- •Упражнение 7.19
- •Упражнение 7.20
- •Упражнение 7.21
- •Упражнение 7.22
- •Упражнение 7.23
- •Упражнение 7.24
- •Упражнение 7.25
- •Упражнение 7.26
- •Упражнение 7.27
- •Упражнение 7.28
- •Упражнение 7.29
- •Упражнение 7.30
- •Упражнение 7.31
- •Упражнение 7.32
- •Упражнение 7.33
- •Упражнение 7.34
- •Упражнение 7.35
- •7.1.6 Приемы автоматизации обработки данных и решения аналитических задач
- •Упражнение 7.36
- •Упражнение 7. 37
- •Упражнение 7.38
- •Упражнение 7.39
- •Упражнение 7.40
- •Упражнение 7.41
- •Упражнение 7.42
- •7.1.7. Работа со списками и сводными таблицами
- •Упражнение 7.43
- •Упражнение 7.44
- •Упражнение 7.45
- •Упражнение 7.46
- •7.2. Вопросы для самоконтроля
Упражнение 7.16
На основе опубликованных в прессе данных о количестве российских граждан, выезжающих за границу с целью туризма, необходимо в книге Продолжение на листе Туризм:
Построить таблицу 7.9 в абсолютных величинах
Таблица 7.9
Страна |
2005 г. |
2004 г. |
2003 г. |
2002 г. |
Турция |
1 562 642 |
1 445 815 |
1 038 593 |
690 098 |
Китай |
1 151 605 |
941 032 |
687 521 |
617 130 |
Египет |
695 676 |
586 375 |
368 405 |
239 712 |
Финляндия |
391 275 |
377 067 |
327 246 |
406 236 |
Украина |
292 200 |
329 946 |
841 692 |
478 560 |
Германия |
231 106 |
255 954 |
278 094 |
208 977 |
Испания |
218 707 |
190 891 |
168 360 |
227 870 |
Италия |
205 566 |
156 093 |
118 981 |
149 710 |
Польша |
200 529 |
322 030 |
422 003 |
635 451 |
Франция |
174 063 |
138 459 |
112 149 |
87 363 |
Построить новую таблицу такой же структуры, но в относительных величинах.
Построить графики динамики поездок россиян в страны, имеющие в таблице нечетные порядковые номера.
По Турции построить объемную круговую диаграмму, поместив сектор с наибольшей величиной на передний план и изменив его цвет на красный.
По своему усмотрению взять данные из табл. 7.9 и по ним построить нестандартную диаграмму. Поместить ее на отдельный лист Рабочей книги.
Результаты работы сохранить в книге Продолжение.
Последнюю построенную диаграмму поместить в Word – документ.
7.1.5. Использование встроенных функций
Функции — заранее определенные в Excel формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Например, функция СУММ суммирует значения в диапазоне ячеек, а функция ТДАТА выдает текущую дату.
Аргументы. Список аргументов может состоять из чисел, текста, логических величин (например, истина или ложь), массивов или ссылок. Аргументы могут быть как константами, так и формулами. Формулы, в свою очередь, могут содержать другие функции
Структура. Любая функция начинается с указания имени функции, затем следует открывающая скобка, указываются аргументы, разделяемые точками с запятыми, а затем — закрывающая скобка. Если написание формулы начинается с функции, перед именем функции вводится знак равенства (=).
Ф ункции (как отмечено в разделе 7.1.2) можно вводить непосредственно в строке формул, т.е. непосредственно в ячейку (рис. 7.25),
Рис. 7.25. Пример ввода функции в строку формул
Однако удобнее использовать Мастер функций, выводящий специальное диалоговое окно (рис. 7.26). В среднем поле этого окна перечислены категории функций, а в нижнем - функции, соответствующие выбранной категории.
Рис. 7.26. Окно Мастера функций с перечнем категорий
Все функции разделены по категориям, чтобы в них было проще ориентироваться (математические, статистические, логические, дата и время, текстовые, финансовые, ссылки и массивы, работа с базой данных, проверка свойств и значений и др.).
В частности, группа математических функций позволяет легко выполнять такие операции над числовыми значениями как возведение в любую степень, извлечение корня любой степени, нахождение логарифма и т.д.
При помощи текстовых функций имеется возможность обрабатывать текст, т.е. находить символы, извлекать нужные символы, записывать символы в строго определенное место текста и др.
С помощью функций даты и времени можно решить задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).
Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения задаваемых условий будут совершать различные виды обработки данных.
Многие функции различаются очень незначительно, поэтому при поиске по категориям полезно воспользоваться краткими описаниями функций, которые предлагает Мастер. В диалоговом окне Аргументы функции Мастер оказывает помощь в задании различных типов аргументов (рис. 7.27).
Рис. 7.27. Диалоговое окно Аргументы функции
Для удобства вычислений функции можно комбинировать. Для этого необходимо задать с помощью Мастера функций первую функцию в формуле. Затем, активизировав строку формул, ввести с клавиатуры знак арифметической операции, которая связывает функции (табл.7.10). Для ввода второй функции также нужно запустить Мастер функций непосредственно из строки формул (пиктограмма ). Можно связать друг с другом произвольное число функций.
Таблица 7.10
Используемые арифметические операторы
Арифметический оператор |
Значение |
Пример |
+ (знак плюс) |
Сложение |
3+3 |
– (знак минус) |
Вычитание |
3–1–1 |
* (звездочка) |
Умножение |
3*3 |
/ (косая черта) |
Деление |
3/3 |
% (знак процента) |
Процент |
20% |
^ (крышка) |
Возведение в степень |
3^2 |
Функции могут являться аргументами других функций (внешних). В формулах можно использовать до семи уровней вложения функций. Для записи такой формулы необходимо ввести обычным образом внешнюю функцию, затем из списка встроенных функций в левой части строки формул выбрать вложенную функцию и указав ее аргументы вернуться к внешней функции, щелкнув ее в строке формул. Вложенная функция, используемая в качестве аргумента. Этот процесс нужно повторить для каждой вложенной функции, если вложений несколько. При этом следует помнить, что аргументы функций отделяются друг от друга знаком точки с запятой.
В обычном режиме Excel вводит в таблицу результаты вычислений по формулам. Можно изменить этот режим и ввести текстовый режим индикации (отображения) формул, выполнив команду Сервис\Параметры и во вкладке Вид в области Параметры окна включить переключатель формулы. После этого на экране появляются не результаты вычислений, а тексты самих формул и функций. При этом ширина столбцов автоматически увеличивается (рис. 7.28).
Рис. 7.28. Окно в режиме отображения формул
Приведенные ниже примеры и упражнения предназначены для показа возможностей наиболее часто употребляемых функций и их эффективности при обработке данных в среде табличного процессора Excel.
Решение математических задач по формулам. Программа позволяет достаточно эффективно решать находить решения нелинейных уравнений, строить по ним графики, находить значения корней и т.д. Поскольку исходными данными для построения графика является табличное представление функции, то прежде чем строить график необходимо рассчитать значение функции для фиксированных значений ее аргумента на конкретном заданном диапазоне. Расчет для значений аргумента, изменяющихся с шагом одинаковой величины (одинаковым шагом), называется табулированием функции.
Например, требуется решить следующее уравнение с одной переменной:
y = x3 - 0,2x2 + 0, 651x + 0,404
Примем, что значения аргумента для табулирования этой функции находятся в диапазоне [-1 : +1] и изменяются с шагом, равным 0,2.
Для наглядности решения уравнения оформим таблицу из двух колонок и проведем табулирование функции. С этой целью на рабочем листе в ячейку A1 введем с клавиатуры текст Функция (X), в ячейку A2 текст “Значение аргумента”, а в ячейку B2 – “Значение функции”. В ячейку A3 введем число -1 (левая граница диапазона изменения аргумента) и, применяя любой прием создания числовой последовательности, заполним диапазон ячеек A3: A13 в виде арифметической последовательности с шагом 0,2 до предельного значения, равного +1 (правая граница диапазона изменения аргумента).
В ячейку B3 введем следующую формулу для расчета значений функции:
= A3 ^3 - 0,2 *A3^2 + 0,651 * A3 + 0,404
и скопируем эту формулу в диапазон ячеек B4 : B13. Таким образом, мы получим ряд вычисленных значений функции для каждого значения аргумента.
Для построения графика функции выполним следующие операции:
выделим область A2 : B13, содержащую табличные значения функции и надписи столбцов;
вызовем Мастер диаграмм, задав команду Вставка\Диаграмма или щелкнув на кнопке <Мастер диаграмм> панели инструментов.
На первом шаге работы Мастера диаграмм в диалоговом окне выбираем закладку Стандартные, в списке Тип – График, в окне Вид – График с маркерами.
На втором шаге:
задаем диапазон ячеек, т.е (A2 :B13);
в закладке Ряд в списке Ряд выбираем Значение аргумента и щелкаем на кнопке <Удалить>;
в поле Имя устанавливаем адрес ячейки, содержащей текст названия таблицы (A1), в поле Подписи оси Х укажем диапазон, содержащий значения аргумента (A3 : A13) и щелкнем по кнопке <Далее>.
На третьем шаге работы Мастера диаграмм:
в закладке Заголовки в поле Ось Х вводим название оси категорий (значение аргумента), в поле Ось Y – название оси значений (значение функции);
в закладке Оси устанавливаем флажки для отображения основных осей;
в закладке Подписи данных установим переключатель <Значения>;
в закладке Таблица данных установим флажок Таблица данных, чтобы исходная таблица отобразилась в области построения диаграммы;
в закладке Легенда зададим положение легенды внизу;
щелкнем на кнопке <Далее>.
На четвертом шаге установим, что диаграмма будем размещена на имеющемся листе и завершим процесс, щелкнув на кнопке <Готово>. При этом окончательный вид решения уравнения и построения графика будет иметь вид как на рис. 7.29.
Рис. 7.29. Пример табуляции функции и графика