1. Введение
В условиях развития глобальных
информационных процессов, решения социально-экономических проблем актуализации
реформы среднего профессионально образования. Соответствующие положения нашли
отражение в принятой в конце 2001 года Правительством Российской Федерации
Концепции модернизации российского образования на период до 2010 года. Концепция
завершает формирование общенациональной образовательной политики Российского
государства, развивая положение законов в области образования и Национальной
Доктрины образования, а также во впервые принятой Министерством образования РФ
за всю историю СПО «Программы развития среднего профессионального образования
России на 2000-2005 годы».
Главные направления данных документов
положены в основу Программы развития Красногорского оптико-электронного колледжа
на 2002-2003 учебный год, Программы информатизации колледжа на 2002-2005 годы. В
этих документах особое внимание удалено созданию новых учебников, методических
пособий. Методическое пособие по работе с программой обработки электронных
таблиц Microsoft Excel и является учебным пособием нового
поколения, призвано помочь студентам овладеть первоначальными навыками и
умениями работы в такой непростой программе как Microsoft Excel. Большинство из существующей
литературы по электронным таблицам носит либо описательный характер, либо
справочный. Научиться работать с электронной таблицей Excel можно, только зная основные
теоретические положения и методы создания таблиц, методику построения графиков и
диаграмм. Работая с данным пособием, студент, не имеющий навыков работы в Excel ,сможет приобрести все необходимые
навыки и умения, начиная с элементарного ввода данных в ячейки, и заканчивая
использованием программы Excel в задачах оптимального
управления. Пособие содержит теоретические сведения, которые помогут начинающему
пользователю глубже понять базовые технологии создания таблиц.
Пособие содержит 15 лабораторных работ:
1.
Применение средств
автоматизации ввода
2. Применение итоговых функций
3. Подготовка и форматирование таблиц
4. Форматирование документа с применением условного
форматирования
5. Построение и
форматирование диаграммы
6. Решение задачи табулирования функции
7. Использование функций в EXCEL
8. Построение сводных таблиц
9. Автоматизация расчётов
10.
Построение графика функции Y=X3-12X2+3
11.
Построение
диаграмм
12. Простейшие операции с
базой данных
13. Получение итоговых
сведений на основе существующей базы данных, построение сводной диаграммы
14. Настройка режима проверки
вводимых данных
15. Решение задачи оптимизации
расходов предприятия
Главная
цель проведения лабораторного практикума – обеспечить прочное и сознательное
освоение основ информационных технологий, формирование практических умений –
профессиональных, учебных, интеллектуальных, необходимых будущему специалисту.
Помимо приобретения чисто практических умений, ценных с точки зрения освоения
компьютерной грамотности, студенты получают наглядное представление о
возможностях, предоставляемых компьютером человеку, выработку при решении
поставленных задач таких профессионально значимых качеств, как
самостоятельность, ответственность, точность, творческая инициатива. После
проведения лабораторных работ студент должен
Уметь:
Производить заполнение ячеек
последовательностью чисел
Производить заполнение ячеек
прогрессией
Производить заполнение ячеек
формулами
Использовать итоговые функции для
вычисления значений, характеризующих набор данных
Строить графики на основе данных,
содержащихся на рабочем листе.
Использовать
функции
Создавать
примечания
Закреплять фрагмент рабочего
листа
Сортировать данные в
таблице
Осуществлять фильтрацию
данных
Создавать итоговые
строки
Использовать электронные таблицы для
автоматизации расчётов
Строить диаграммы, используя электронные
таблицы
Использовать электронную таблицу Excel в качестве базы
данных
Выполнять операции
сортировки и фильтрации по какому-либо признаку
Создавать сводные диаграммы
на основе сводных таблиц
Использовать средства
автоматической проверки вводимых данных
После каждой темы в пособии содержатся варианты
проверочных заданий, что позволяет контролировать навыки и умения студентов,
приведено большое количество рисунков, все важные понятия выделены жирным
шрифтом.
В пособии затронуты вопросы автоматизации приложений
посредством записи последовательности команд и действий пользователя – макросов.
Приведёны варианты проверочных упражнений по созданию макросов.
Пособие также содержит 20 вариантов зачётных заданий
по основным темам практикума: построение графика функций с двумя и тремя
условиями, применение относительной и абсолютной адресации, запись макросов.
В повседневной жизни человек постоянно
использует таблицы: дневник в школе, расписание электричек, расписание занятий и
т.д. Персональный компьютер расширяет возможности использования таблиц за счёт
того, что позволяет не только представлять данные в электронном виде, но и
обрабатывать их. Класс программного обеспечения, используемый для этой цели,
называется табличными процессорами
или электронными таблицами. Основное
назначение табличных процессоров – обработка таблично организованной информации,
проведение расчётов на её основе и обеспечение визуального представления
хранимых данных и результатов их обработки в виде графиков,
диаграмм.
Применение электронных таблиц упрощает
работу с данными и позволяет получать результаты без проведения расчётов
вручную. Расчёт по заданным формулам выполняется автоматически. Изменение
содержимого, какой-либо ячейки приводит к перерасчёту значений всех ячеек,
которые связаны с ней формульными отношениями. Электронные таблицы используются
во всех сферах человеческой деятельности, но особо широко используются для
проведения экономических и бухгалтерских расчётов. Их можно эффективно
использовать, например, для:
Автоматизации итоговых
вычислений
Подготовки табличных
документов
Ведения простых баз данных и их
обработки
Построения диаграмм и
графиков
Создания сводных таблиц
Совместной работы с экономическими и
бухгалтерскими документами
Среди наиболее известных программных
приложений, предназначенных для ведения электронных таблиц, могут быть названы
SuperCalc, Lotus 1-2-3, Quattro Pro. Программа Microsoft Excel, входящая в состав офисного пакета
Microsoft Office 2000, занимает лидирующее
положение среди электронных таблиц, совмещая в себе преимущества как электронных
таблиц со средствами финансового и статистического анализа, так и средств
визуального программирования посредством встроенного языка программирования
Visual Basic for Applications (VBA). VBA позволяет автоматизировать весь
комплекс работ от сбора информации, её обработки, анализа до создания отчётной
документации и её публикации как для офисного пользования, так и на Web-узлах.
1.2
Основные понятия электронных
таблиц
Документ, созданный в MS Excel, называется рабочей книгой. Рабочая книга состоит из набора
рабочих листов. Листов в книге
Excel может быть до 255. Каждый рабочий
лист имеет имя, по умолчанию Лист1,
Лист2, Лист3. Названия листов отображаются в нижней части листа. С помощью
ярлычков, на которых написаны имена листов, можно переключаться между рабочими
листами, входящими в рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть на его
ярлычке, либо нажать правой кнопкой
мыши на ярлычке и выбрать пункт Переименовать. Листам можно давать произвольные имена
длиною до 31-го символа, исключая символы * : / \ ? [ ]
Рисунок 1
Чтобы переместить рабочий лист в другую позицию в той же
рабочей книге, нужно мышью перетащить его ярлычок в соответствующее
место.
Если пользователю необходимо добавить новый
рабочий лист, он может сделать это, выполнив команду Вставка / Лист. Для удаления листа следует выполнить
команду Правка / Удалить лист, при
этом необходимо помнить о том, что удалённый лист восстановить
невозможно.
Пространство рабочего листа состоит из
строк и столбцов. По умолчанию каждый рабочий лист имеет
256 столбцов, (каждый столбец имеет имя, озаглавленное латинской буквой) и 65536
строк (нумерация от 1 до 65536), таким образом, всего на листе имеется 16777216
ячеек.
На пересечении строк и столбцов образуются
ячейки таблицы. Ячейка – это минимально адресуемый элемент
рабочего листа. Имя ячейки (адрес)
состоит из имени столбца и номера строки, например, A10 или D23. Адресация ячеек используется при
записи формул. Одна из ячеек всегда является активной, и в ней производятся операции ввода и
редактирования. Группа ячеек называется диапазоном. Диапазон ячеек обозначают, указывая через
двоеточие номера ячеек, расположенных в противоположных углах, например:
А5:C20.
Выделение
ячеек с помощью мыши
Таблица 1
|
Чтобы
выделить: |
Действия
пользователя |
|
Лист |
Щёлкнуть мышью на сером
прямоугольнике, расположенном на пересечении строки с номером 1 и столбца
с именем А |
|
Столбец |
Щёлкнуть на заголовке столбца (на
имени столбца) |
|
Строку |
Щёлкнуть на номере
строки |
|
Ячейку |
Щёлкнуть на ячейке
мышью |
|
Диапазон несмежных
ячеек |
Выделить мышью первый диапазон.
Удерживая клавишу CTRL, выделить другой
диапазон и т. д. |
1.3
Ввод, редактирование и
форматирование данных
Ввод
текста и чисел
Ввод данных осуществляется непосредственно
в текущую ячейку или в строку формул. Содержимое строки формул всегда
соответствует содержимому текущей ячейки, так что оба способа равнозначны.
Программа Excel может работать с тремя типами
данных, которые могут размещаться в ячейках электронной таблицы - это текст,
число и формула. Текст по умолчанию
выравнивается по левому краю ячейки, а числа по правому.
Ввести данные в
ячейку можно несколькими способами, например:
Сделать ячейку активной, то есть поместить
курсор в нужную ячейку и один раз щёлкнуть левой клавишей
мыши.
Ввести данные с
клавиатуры
Нажать клавишу Enter
или
Сделать двойной щелчок мышью на нужной
ячейке
Ввести данные с
клавиатуры
Нажать клавишу Enter
Редактировать данные можно также несколькими способами,
например:
Для
замены одних данных в ячейке другими
следует сделать её активной и ввести новые данные.
или
Сделать активной ячейку
Выполнить двойной щелчок на редактируемой
ячейке
или
Сделать активной ячейку
Нажать клавишу F2
или
Сделать активной ячейку
Сделать щелчок в строке
формул
Для удаления содержимого ячейки или диапазона ячеек
необходимо выделить очищаемую ячейку или диапазон и нажать клавишу Delete.
1.5 Выравнивание и изменение ориентации
текста и чисел
Excel позволяет выравнивать текст и числа, как по горизонтали (влево, вправо, по центру), так и по
вертикали (по верхнему краю, посередине,
нижнему краю). Кроме того, пользователь может управлять углом поворота текста и
переносить слова в ячейке.
Рисунок 2
Чтобы выровнять содержимое ячейки или диапазона:
Выберите ячейку или
диапазон
Выполните команду Формат / Ячейки. На экране отобразится диалоговое окно
Формат ячеек
(рис.3)
Рисунок 3
Используя элементы управления вкладки
Выравнивание этого диалогового окна,
можно установить то выравнивание, которое необходимо.
1.5 Форматирование содержимого
ячеек
Визуально человеку проще работать с теми
данными, которые представлены в отформатированном виде. Для того чтобы
отформатировать данные по любому формату, имеющемуся в Excel необходимо:
Выделить ячейку или диапазон ячеек,
подлежащих форматированию.
Выполнить команду Формат / Ячейки. Откроется диалоговое окно Формат
ячеек (рис.4)
Выберите вкладку Число этого
диалогового окна и установите тот тип формата, который вам нужен
Рисунок 4
1.6
Основные типы
форматов
Таблица 2
|
Формат |
Описание |
|
Общий |
Этот формат принят по
умолчанию |
|
Числовой |
Число отображается с заданным
количеством десятичных знаков после запятой |
|
Денежный |
После изображения числа может
помещаться знак валюты |
|
Финансовый |
Отличается от денежного вынесением
знака минус для отрицательных чисел в крайнюю левую позицию
ячейки |
|
Дата |
Эта категория позволяет вывести дату
по одному из пятнадцати форматов |
|
Время |
Эта категория позволяет вывести время
по одному из восьми форматов |
|
Процентный |
Этот формат позволяет выводить число
в виде процентов со знаком % и определять количество выводимых знаков
после десятичной точки |
|
Дробный |
Дробный формат используется для
представления чисел в виде смешанного числа |
|
Экспоненциальный |
В этом формате число представляется в
виде mE±p, где m – мантисса числа, Е – символ,
обозначающий основание десятичной ССЧ, p – порядок числа, например число
5,34´1025
записывается как 5.34Е+25 |
|
Текстовый |
Применение этого формата к числовому
значению позволяет рассматривать его как текст |
|
Дополнительный |
Дополнительный формат предназначен
для работы с почтовыми индексами, телефонными номерами,
адресами |
|
Все форматы |
Эта категория позволяет создавать
пользовательские форматы |
1.7
Вычисления в электронных
таблицах
Вычисления в таблицах осуществляются при помощи
формул.
Формула
может содержать числовые константы, ссылки на ячейки и функции Excel, соединённые знаками
математических операций, операций сравнения, операций конкатенации. Формула,
введённая в ячейку, должна начинаться со знака равенства.
Примеры
формул
Таблица 3
|
Формула |
Описание |
|
=В1+В2 |
Складывает содержимое
ячеек В1 и В2 |
|
=А1^(1/3) |
Возводит в степень
(1/3) содержимое ячейки А1 |
|
=СУММ(А1:А5) |
Возвращает сумму
значений из диапазона А1:А5 |
При вводе в формулу ссылки на ячейку или
диапазон ячеек можно не набирать эту ссылку с клавиатуры, а выбрать нужную
ячейку или диапазон ячеек мышью методом выделения.
1.8 Ошибки, возвращаемые
формулами
Таблица 4
|
Ошибка |
Описание |
|
#Н/Д |
Н/Д – является сокращением термина
Неопределённые Данные. Помогает предотвратить использование ссылки на
пустую ячейку |
|
#ЧИСЛО! |
Функция с числовым аргументом
использует неприемлемый аргумент |
|
#ИМЯ? |
Ошибка в написании имени или
используется несуществующее имя |
|
#ССЫЛКА! |
Используется ссылка на несуществующую
ячейку |
|
#ЗНАЧ! |
Вместо числового или логического
значения введён текст, и Excel не может
преобразовать его к нужному типу данных |
|
#ДЕЛ/0! |
В качестве делителя используется
ссылка на ячейку, в которой содержится ненулевое или пустое значение (если
ссылкой является пустая ячейка, то её содержимое интерпретируется как
ноль) |
|
#ПУСТО! |
Используется ошибочная ссылка на
ячейку |
|
############# |
Данные не помещаются в ячейку по
ширине, следует увеличить ширину
столбца |
1.9 Ссылки на
ячейки
На рис.5 представлена формула для сложения двух
чисел. Вместо чисел в формуле используются имена ячеек или, как говорят, ссылки на ячейки.
Ссылки выполняют роль адресов ячеек, содержимое которых используется в
вычислениях. Ссылку на ячейку можно задать несколькими способами:
Ввести адрес ячейки с
клавиатуры
Выполнить щелчок на нужной ячейке или
выделить мышью нужный диапазон ячеек.
Рисунок 5
В Excel при работе с формулами
используется два типа адресации ячеек: относительная и абсолютная.
По умолчанию, ссылки на ячейки в формулах
рассматриваются как относительные. Это означает, что адреса ячеек при
копировании формул из одной ячейки в другую изменяются автоматически, то есть
меняются относительно исходной формулы. Как видно из примера на рис.6 при
копировании формулы, которая находится в ячейке С1, происходит автоматическая
переадресация ячеек.
Рисунок 6
При абсолютной адресации адреса ячеек при
копировании формулы не изменяются. На рис. 7 показан пример из которого видно,
что если копировать формулу =В2*В4, которая находится в ячейке С4 обычным
способом с помощью маркера автозаполнения, то результат будет неверным, потому
что адресация будет меняться относительно исходной формулы. А нам нужно, чтобы
адрес ячейки B2 не изменялся. Для того чтобы создать
абсолютную ссылку на ячейку, достаточно перед именем строки и столбца поставит
знак $. (рис.8)
Рисунок 7
Рисунок 8
Кроме абсолютной ссылки на ячейку, имеются ещё два
типа абсолютных ссылок:
Абсолютная ссылка на строку. В этом случае
знак $ ставится только перед номером строки. Например, С$4 – это абсолютная
ссылка на четвёртую строку.
Абсолютная ссылка на столбец. В этом случае
знак $ ставится только перед именем столбца. Например, $С4 – это абсолютная
ссылка на столбец С.
В качестве примера можно привести расчёт
таблицы умножения (рис. 9). В ячейку В2 следует ввести формулу: =$A2*B$1 и растянуть её за маркер заполнения
на диапазон B2:J10.
Рисунок 9
1.10 Копирование
содержимого ячеек
Копирование и перемещение ячеек в Excel можно осуществить способом
перетаскивания или через буфер обмена. При работе с небольшим числом ячеек
удобно использовать способ перетаскивания, при работе с большими диапазонами
удобнее пользоваться буфером обмена.
Чтобы способом перетаскивания
скопировать содержимое ячейки,
следует поставить указатель мыши на границу текущей ячейки (указатель мыши
примет вид белой стрелки) и, удерживая клавишу CTRL, перетащить в нужное место рабочего
листа содержимое ячейки. Чтобы
способом перетаскивания переместить содержимое ячейки, следует поставить
указатель мыши на границу текущей ячейки (указатель мыши примет вид белой
стрелки) и перетащить в нужное место рабочего листа содержимое ячейки.
7 команды Правка / Копировать и Правка / Вставить (копирование) или Правка / Вырезать и Правка / Вставить (перемещение).
Можно также использовать перетаскивание с
помощью правой кнопки мыши. В этом случае при отпускании кнопки появляется
специальное меню, из которого можно выбрать конкретную выполняемую команду
(рис.10)
Рисунок 10
Действия по копированию и перемещению
содержимого ячеек можно выполнить, используя соответствующие кнопки на
стандартной панели: Копировать, Вырезать и Вставить.
Копирование и перемещение данных через
буфер обмена нужно выполнять следующим образом: необходимо выделить копируемый
(вырезаемый) диапазон и дать команду на его помещение в буфер обмена: Правка
/ Копировать или Правка / Вырезать. Место вставки определяется путём указания
ячейки, соответствующей верхнему левому углу диапазона, помещённого в буфер
обмена. Вставка выполняется командой Правка / Вставить.
