Создание многостраничного документа в табличном процессоре microsoft excel
Цель занятия. Применение относительной и абсолютной адресации ячеек для финансовых расчетов. Сортировка, условное форматирование и копирование созданных таблиц. Работа с листами электронной книги.
Инструментарий. ПЭВМ IBM PC, программа MS Excel.
Литература.
1. Информационные технологии в профессиональной деятельности: учебное пособие/ Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия», 2004.
2. Практикум по информационным технологиям в профессиональной деятельности: учебное пособие-практикум / Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия», 2004.
ЗАДАНИЯ
Задание 1. Создать таблицы ведомости начисления заработной платы за два месяцана разных листах электронной книги, произвести расчеты, форматирование, сортировку и защиту данных
Исходные данные представлены на рис.1, результаты работы на рис.6.
1. Откройте редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
2. Создайте на Листе 1 таблицу расчета заработной платы по образцу (рис.1).
Выделите отдельные ячейки для значений % Премии (D4) и %Удержания (F4).
Введите исходные данные – Табельный номер, ФИО и Оклад; % Премии = 27%, %Удержания = 13%
Произведите расчеты во всех столбцах таблицы.
При расчете Премии используется формула Премия = Оклад *%Премии,
в ячейке D5 наберите формулу =$D$4 * C5(ячейка D4 используется в виде абсолютной адресации).
Скопируйте набранную формулу вниз по столбцу автозаполнением.
Краткая справка. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную ячейку окрашенная ячейка с константой будет вам напоминанием, что следует установить абсолютную адресацию (набором с клавиатуры в адресе символов $ или нажатием клавиши [F4]).
Рис.1. Исходные данные для Задания 1.
Формула для расчета «Всего начислено»:
Всего начислено = Оклад + Премия
При расчете "Удержания" используется формула:
Удержания = Всего начислено * %Удержаний,
в ячейке F5 наберите формулу = $F$4 * E5
Формула для расчета столбца «К выдаче»:
К выдаче = Всего начислено – Удержания
3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доход по данным колонки «К выдаче» (Вставка/ Функция/ категория Статистические).
4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши.
Результаты работы представлены на рис.2.
Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.
Рис. 2. Итоговый вид таблицы расчета заработной платы за октябрь
5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/ Переместить/ скопировать лист). Можно воспользоваться командой Переместить/ скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию (рис. 3).
Рис.3. Копирование листа электронной книги
Краткая справка. Перемещать и копировать листы можно, перетаскивая их ярлыки (для копирования удерживайте нажатой клавишу [Ctrl]).
6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32%. Убедитесь, что программа произвела пересчет формул.
7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (выделите столбец Е «Всего начислено» и выполните команду Вставка/ Столбцы);
рассчитайте значение доплаты по формуле Доплата = Оклад * %Доплаты. Значение доплаты примите равным 5%.
8. Измените формулу для расчета значений колонки «Всего начислено»:
Всего начислено = Оклад + Премия + Доплата.
Скопируйте формулу вниз по столбцу.
9. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 - зеленым цветом шрифта, меньше или равно 7000 – красным цветом шрифта, больше или равно 10000 – синим цветом шрифта (Формат/ Условное форматирование) (рис.4).
Рис.4. Условное форматирование данных
10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы с 5 по 18 строки без строки «Всего», выберите меню Данные/ Сортировка, сортировать по - Столбец В) (рис.5).
Рис.5. Сортировка данных
11. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/ Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.
Конечный вид таблицы расчета заработной платы за ноябрь приведен на рис.6.
Рис.6. Конечный вид таблицы расчета зарплаты за ноябрь
12. Сохраните созданную электронную книгу под именем Зарплата.
Excel 4. СВЯЗАННЫЕ ТАБЛИЦЫ. РАСЧЕТ ПРОМЕЖУТОЧНЫХ ИТОГОВ В ТАБЛИЦАХ MS EXCEL
Цель занятия. Изучение технологии связывание листов электронной книги. Расчет промежуточных итогов. Структурирование таблиц.
Инструментарий. ПЭВМ IBM PC, программа MS Excel.
Литература.
1. Информационные технологии в профессиональной деятельности: учебное пособие/ Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия», 2004.
2. Практикум по информационным технологиям в профессиональной деятельности: учебное пособие-практикум / Елена Викторовна Михеева. – М.: Образовательно-издательский центр «Академия», 2004.
ЗАДАНИЯ
Задание 1. Рассчитать зарплату за декабрь и построить диаграмму. Создать итоговую таблицу ведомости квартального начисления заработной платы, провести расчет промежуточных итогов по подразделениям.
1. Откройте редактор электронных таблиц Microsoft Excel и откройте созданный в практической работе 3 файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги (Правка/ Переместить/скопировать лист).
Не забудьте для копирования поставить галочку в окошке Создавать копию.
3. Присвойте скопированному листу название «Зарплата декабрь». Подправьте название месяца в названии таблицы.
4. Измените значение Премии на 46%, Доплаты – на 8%. Убедитесь, что программа произвела пересчет формул (рис.1).
Рис.1. Ведомость зарплаты за декабрь
5. По данным таблицы «Зарплата декабрь» постройте гистограмму дохода сотрудников (по столбцу «К выдаче (руб.)»). В качестве подписей оси Х выберите фамилии сотрудников.
Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис.2.
Рис.2. Гистограмма зарплаты за декабрь
6. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в таблице расчета зарплаты за октябрь (выделите фрагмент таблицы с 5 по 18 строки без строки «Всего», выберите меню Данные/ Сортировка, сортировать по – Столбец В).
7. Скопируйте содержимое листа «Зарплата декабрь» на новый лист (Правка/Переместить/скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.
8. Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».
9. Отредактируйте лист «Итоги за квартал» согласно образцу на рис.3.
Для этого удалите в основной таблице (см. рис.1) колонки оклада, премии и доплаты, а также строку 4 с численными значениями %Премии и %Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего дохода под основной таблицей. Вставьте пустую третью строку.
10. Вставьте новый столбец «Подразделение» (Вставка/ Столбцы) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу (рис. 3).
Рис. 3. Образец таблицы
11. Произведите расчет квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа).
Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по вкладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа.
В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид:
= 'Зарплата декабрь'!F5 +' Зарплата ноябрь'!F5 + 'Зарплата октябрь'!E5.
Аналогично произведите квартальный расчет столбца «Удержания» и «К выдаче».
Примечание. При выборе начислений за каждый месяц делайте ссылку на соответствующую ячейку из таблицы соответствующего листа электронной книги «Зарплата». При этом произойдет связывание ячеек листов электронной книги.
12. В силу однородности расчетных таблиц зарплаты по месяцам для расчета квартальных значений столбцов «Удержания» и «К выдаче» достаточно скопировать формулу из ячейки D5 в ячейки E5 и F5 (рис.4).
Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы вниз по столбцам D, E и F. Ваша электронная таблица примет вид, как на рис. 4.
Рис. 4. Вид таблицы после проведения расчетов
13. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений – по фамилиям. Таблица примет вид, как на рис. 5.
Рис.5 Вид таблицы после сортировки
14. Рассчитайте промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/ Итоги (рис. 6.).
Задайте параметры подсчета промежуточных итогов:
при каждом изменении в - Подразделение;
добавить итоги по: - Всего начислено, Удержания, К выдаче.
Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».
Рис. 6. Окно задания параметров расчета промежуточных итогов
Примерный вид итоговой таблицы представлен на рис.7.
Рис.7. Итоговый вид таблицы расчета квартальных итогов по зарплате
15. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы.
Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и
«-» ).
Краткая справка. Под структурированием понимается многоуровневая группировка строк и столбцов таблицы и создание элементов управления, с помощью которых легко можно скрывать и раскрывать эти группы.
Microsoft Excel – самая популярная в мире программа электронных таблиц, входящая в стандартный офисный пакет Microsoft Office. Она выдержала уже несколько переизданий, возможности каждой новой версии расширяются, появляются новые формулы, макросы для вычислений, которые используются в бухгалтерском учете, научных и инженерных приложениях. MS Excel – универсальная программа для составления электронных таблиц любой сложности и дизайна, графиков и диаграмм, поэтому современные офисные работники готовят в ней отчеты, экспортируют в Excel данные из других офисных программ.
Приложение Excel, будучи составной частью популярного пакета (имеется в виду Microsoft Office), по массовости своего использования уступает, пожалуй, только другому приложению этого же пакета (имеется в виду редактор Word). Хотя это утверждение основано и не на статистических данных, однако, думается, выглядит вполне правдоподобно. В любом случае, малознакомым широкому кругу пользователей приложение Excel никак не назовешь. У Microsoft Excel есть существенный, и, как представляется, не до конца раскрытый потенциал, и задача данного пособия состоит в том, чтобы показать возможности MS Excel при решении прикладных задач.
Основные функции Excel:
− проведение различных вычислений с использованием мощного аппарата функций и формул;
− исследование влияния различных факторов на данные; − решение задач оптимизации;
− получение выборки данных, удовлетворяющих определенным критериям;
− построение графиков и диаграмм.
Общие сведения о книгах и листах Microsoft Excel
При запуске Excel открывается рабочая книга с пустыми рабочими листами. Рабочая книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Такие файлы имеют расширение - .xlsх. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. Имена листов отображаются на ярлычках в нижней части окна книги. Для перехода с одного листа на другой следует указать соответствующий ярлычок. Название активного листа выделено жирным шрифтом. Рабочий лист - это собственно электронная таблица, основной тип документа, используемый в Excel для хранения и манипулирования данными. Он состоит из ячеек, организованных в столбцы и строки, и всегда является частью рабочей книги. В рабочем листе Excel 2007имеется 16 384 столбца, заголовки которых указаны в верхней части листа с помощью букв английского алфавита и 1048576 строк. Столбцы по умолчанию озаглавлены буквами, строки - цифрами. Щелкните мышью на любой ячейке вашего рабочего листа и, таким образом, вы сделаете ее текущей или активной (она пометится рамкой). В поле имени, будет показан адрес текущей ячейки, например В4. Ячейка - это основной элемент электронной таблицы только в ней может содержаться какая-либо информация (текст, значения, формулы).
Строка меню.
Под строкой заголовка находится строка меню, в которой перечислены группы команд: Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид. Каждая группа объединяет набор команд, имеющих общую функциональную направленность.
Рис. 1.2. Вид строки меню
Понятие формулы
Формулы – это выражение, начинающееся со знака равенства «═» и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам арифметических операций, которые используются в Excelотносятся:сложение; вычитание; умножение; деление; возведение в степень.
Некоторые операции в формуле имеют более высокий приоритет и выполняются в такой последовательности:
возведение в степень и выражения в скобках;
умножение и деление;
сложение и вычитание.
Результатом выполнения формулы является значение, которое выводится в ячейке, а сама формула отображается в строке формул. Если значения в ячейках, на которые есть ссылки в формулах, изменяются, то результат изменится автоматически.
В формуле может быть указана ссылка на ячейку, если необходимо в расчетах использовать её содержимое. Поэтому ячейка, содержащая формулу, называется «зависимой ячейкой», а ячейка содержащая данное – «влияющей ячейкой». При создании на листе формул можно получить подсказку о том, как связаны зависимые и влияющие ячейки. Для поиска таких ячеек служат команды панели инструментов «Зависимости». Значение зависимой ячейки изменится автоматически, если изменяется значение влияющей ячейки, на которую в формуле есть ссылка. Формулы могут ссылаться на ячейки или на диапазоны ячеек, а также на их имена или заголовки.
Таблица 1
Ширина ячейки недостаточна для отображения результата вычисления или отрицательный результат вычислений в ячейки, отформатированной как данные типа даты и времени
Нервный тип аргумента или операнда. Например, указание в качестве аргумента ячейки с текстом, когда требуется число
Еxcel не может распознать текст, введённый в формулу, например неверное имя функции
Данные ячейки одного из аргументов формулы в данный момент доступны
Неверная ссылка на ячейку
Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейки
Результат поиска пересечений двух непересекающихся областей, то есть неверная ссылка
Краткие теоретические сведения.
Рабочие книги и рабочие листы Microsoft Excel.
Чтобы начать работать в Microsoft Excel нажмите кнопку Пуск → Программы → Программы Microsoft Office → Microsoft Excel . При запуске программы Excel открывается окно, содержащее новую рабочую книгу. Рабочая книга – это многостраничный документ Excel , каждая страница которого называется рабочим листом . В окне документа отображается активная страница . Пользователь может вставить в книгу дополнительные листы (максимально – 255).
Рабочее окно Excel (рис. 2) содержит стандартную строку заголовка, меню и панели инструментов. Под ними расположена панель, в составе которой имеются поле имени и строка формул .
Строка состояния , находящаяся в нижней части окна программы Excel , выдаёт информацию о состоянии программы, режимах её работы и выполняемых операциях. Она также информирует пользователя о том, что можно вводить данные или выполнять команду. В этом случае в ней отображается слово «Готово».
Электронная таблица (ЭТ или Excel ) представляет собой прямоугольную сетку, содержащую 256 столбцов и 65536 строк, расположенных на рабочем листе. Пересечение столбца и строки образует ячейку - основной элемент ЭТ для хранения данных. По умолчанию строки нумеруются, а столбцы обозначаются одной или двумя латинскими буквами.
Каждая ячейка имеет свой адрес, он определяется именем столбца и номером строки, которым принадлежит ячейка (например А1, В6 и др.). Активная ячейка высвечивается в поле имени и она окружена рамкой, которая называется рамкой активной ячейки , а заголовки столбца и строки этой ячейки подсвечиваются. В каждый момент времени активной является только одна ячейка , и данные можно вводить лишь в неё.
При работе в Excel вид указателя мыши постоянно меняется при его перемещении по рабочему листу и зависит от режима работы программы и доступных команд.
Когда указатель мыши имеет вид «контурного» толстого креста , его можно использовать для выделения ячеек.
В правом нижнем углу рамки активной ячейки указатель мыши принимает вид тонкого чёрного креста . Он указывает на возможность выполнения операции заполнения смежных ячеек.
При установке указателя на границе активной ячейки появляется четырёхнаправленная стрелка . Этот указатель используется для перемещения содержимого ячейки в другое место.
При работе в Excel очень внимательно следите за указателем мыши, так как изменение его положения всего на миллиметр может привести к изменению его формы и, следовательно, функциональных возможностей.
Ввод данных.
В ячейки рабочего листа могут быть введены данные трёх типов:
- числа (к ним относятся также значения даты и времени), представляющие собой величины, используемые в вычислениях;
- формулы , которые являются инструкциями , вводимыми в ячейки, в соответствии с которыми производятся вычисления;
- текст, информация, не являющаяся ни числом, ни формулой. Текст обрабатывается как последовательность символов, даже если он представляет собой набор цифр.
Данные могут вводиться только в активную ячейк у – либо непосредственно, либо с использованием строки формул, которая расположена под панелью инструментов в верхней части экрана.
Если при вводе произошла ошибка , щёлкните в строке формул на кнопке Cancel (Отмена) с изображением крестика красного цвета . Или нажмите клавишу Esc на клавиатуре.
Для того чтобы закончить ввод данных нажмите кнопку Enter (Ввод) – изображена в виде галочки зелёного цвета в строке формул или клавишу Enter на клавиатуре для завершения ввода или перехода к другой ячейке.
Редактирование данных.
Существуют два способа изменения содержимого ячейки.
При вводе в выбранную ячейку нового значения старая информация заменяется вводимой.
Если необходимо очистить содержимое ячейки : выделите ячейку и нажмите клавишу Delete или щёлкните на ячейке правой кнопкой мыши и в открывшемся контекстном меню выберете пункт «Очистить содержимое».
! Не вызывайте команду Delete (Удалить) , поскольку при этом ячейки удаляются с листа, а соседние ячейки сдвигаются и занимают их место.
С помощью строки формул, если ячейка содержит громоздкую формулу или длинную строку символов, которые необходимо лишь незначительно скорректировать. Для этого сначала щёлкните на ячейке и установите курсор ввода в строку формул, а затем воспользуйтесь мышью или клавиатурой и внесите правку. Перейти в режим редактирования можно нажатием функциональной клавиши F 2 или двойным щелчком мыши на ячейке, после чего можно вносить изменения прямо в нее.
Форматирование данных.
Все действия по форматированию можно выполнить, пользуясь вкладками Число, Шрифт, Выравнивание, Вид и Граница в Меню → Формат → Ячейки .
С помощью этих команд можно поменять внешний вид данных в таблице, изменяя шрифт, размер, стиль и цвет информации, которая выводится в ячейки и текстовые поля.
Для изменения типа вводимых в ячейку данных используется команда Меню → Формат → Ячейки → вкладка Число , где изменяются числовые форматы активной ячейки. Это же можно сделать с помощью кнопок панели инструментов форматирования.
Чаще всего использующиеся операции форматирования выведены в виде кнопок на панель инструментов Форматирование : .
Наибольший интерес представляют кнопки:
- кнопка «Объединить и поместить в центре» . Она используется для создания заголовков путём объединения нескольких ячеек в одну.
- кнопки «денежный и процентный формат» используются для преобразования числовых значений в соответствующие форматы чисел.
- кнопка «формат с разделителем» преобразует большие числа к наиболее удобному для восприятия виду. Например: 300000 преобразуется к 300 000, при этом меняется только вид числа, а не само число.
- кнопки «Увеличить/Уменьшить разрядность» числа. При уменьшении разрядности в отображаемом числе уменьшается количество значащих цифр после десятичной запятой. Однако не следует путать понятия «форматирование» и «округление». Когда число отформатировано, оно только кажется округлённым. При создании ссылки на ячейку с отформатированным числом оно будет применяться в вычислениях в исходном виде, то есть со всеми знаками после запятой.
Создание новой таблицы.
Для создания новой таблицы нужно:
Ввести сначала названия столбцов в выбранную в качестве начала таблицы новую строку (шапка таблицы).
Определиться с выравниванием названий в шапке таблицы ( Меню → Формат → Ячейки → вкладка Выравнивание ).
Аналогичные действия проделать со строками.
Создать обрамление таблицы (этот пункт смотри ниже).
Обрамление таблицы.
Excel позволяет затенить некоторые ячейки или заключить их в рамку. Можно рисовать произвольные комбинации горизонтальных и вертикальных линий слева, справа, вверху или внизу ячейки, а также задавать двойное подчеркивание и пунктирное. Эти операции выполняются в режиме Меню → Формат → Ячейки → вкладка Граница или с помощью кнопки Границы панели инструментов Форматирование .
Для оформления таблиц также можно использовать автоформатирование (команду Автоформат в Меню → Формат ), которое позволяет выбрать стандартный формат для таблицы.
Выделение ячеек, их диапазонов, строк и столбцов.
Операция выделения выполняется для обозначения ячеек, к которым должна относиться следующая команда или действие. В Excel всегда выделена рамкой по крайней мере одна – активная ячейка. Группа выделенных ячеек называется – диапазоном . Для выделения диапазона ячеек переместите указатель в первую ячейку диапазона и, удерживая кнопку мыши, перетащите его в нужном направлении до последней ячейки, которую необходимо включить в диапазон, затем отпустите кнопку. Или с помощью клавиатуры: поместите указатель ячейки в начало выделяемой области, нажмите клавишу Shift , а затем, удерживая её, расширите выделенный диапазон, используя клавиши управления курсором.
Для выделения одного столбца (строки) достаточно щёлкнуть на его заголовке.
Объединение и пересечение – особые виды диапазонов ячеек.
Объединение – это область, содержащая все ячейки, принадлежащие нескольким диапазонам.
Например: при вычислении суммы чисел, находящихся в ячейках С2:С8 и С20:С28, в аргументе функции следует записать выражение С2:С8;С20:С28. Точка с запятой в данном случае используется для обозначения объединения всех ячеек в обоих диапазонах.
Пересечение ячеек – это область, которая содержит ячейки, принадлежащие одновременно нескольким диапазонам. Для обозначения пересечения вместо точки с запятой используется пробел.
Например: пересечение двух диапазонов, задаваемое как С2:С10 А10: J 20, содержит только одну ячейку – С10.
Копирование данных или формул в смежные ячейки.
Чтобы скопировать данные или формулу можно воспользоваться командами копирования вставки в Меню → Правка → Вставить или Специальная вставка. Используя пункт Специальная вставка можно скопировать только значения, только формулы или только форматы копируемых ячеек или др.
Так же скопировать данные или формулу из ячейки возможно соответствующими кнопками на панели инструментов или с помощью тонкого чёрного креста, появляющегося в правом углу активной ячейки, путём передвижения его на смежные ячейки .
Ввод данных через формы.
Форма данных представляет собой диалоговое окно, позволяющее легко просматривать, вводить, изменять, добавлять и удалять строки или находить строки таблицы, удовлетворяющие заданному критерию.
С помощью формы данных можно просматривать в каждый момент времени только одну полную запись таблицы (строку). Форма данных автоматически создается на основе имен столбцов. Чтобы вывести форму на экран необходимо выделить любую ячейку списка (таблицы) и выбрать команду Меню → Данные → Форма .
Редактирование данных выполняется так же, как редактирование текста в любом поле ввода. Содержимое вычисляемых столбцов, например ячеек с промежуточными итогами, отображается, как обычные надписи, и недоступно для редактирования.
Форму данных можно использовать при поиске отдельных записей , удовлетворяющих заданным условиям. Для этого необходимо щёлкнуть на кнопке «Критерии». В результате откроется чистое окно формы записей. Введите в соответствующие текстовые поля информацию, используемую в качестве критерия поиска. Щёлкните на кнопке «Далее» для поиска первой записи, удовлетворяющей данным условиям. При очередном щелчке на этой кнопке произойдёт переход к следующей подходящей записи.
Одним из недостатков ввода данных через формы является то, что она работает только с таблицами, у которых шапка (имена столбцов) отображена только на одной строке. В случае таблицы со сложной шапкой Excel не сможет создать форму.
Обработка числовых значений.
Все вычисления в Excel выполняются с помощью формул. Формула может содержать ссылки на ячейки, константы, обращения к функциям, а также знаки арифметических и логических операций. Excel использует стандартные символы арифметических и логических операций.
При вычислении результата формулы используется порядок, принятый в математике. Для его изменения можно применять круглые скобки.
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
311 лекций для учителей,
воспитателей и психологов
Получите свидетельство
о просмотре прямо сейчас!
Практическая работа «Создание документов в MS Excel»
Выполнив задания этой темы, вы научитесь:
- Технологии создания табличного документа;
- Вводить в ячейки данные разного типа: текстовые, числовые.
Задание : Выполните в таблице Excel ввод необходимых данных и отредактируйте ее. Образец для создания документа представлен ниже.
Технология выполнения задания:
1. Запустите программу Microsoft Excel.
2. В соответствующих ячейках введите текст и цифры из образца. Отредактируйте введенные данные. Зафиксируйте данные в ячейке любым известным вам способом. Используйте различные форматы представления данных.Если в ячейку вводите текст, то тип данных выставите «текстовый». Если в ячейку вводите числа, то тип данных выставите «числовой». Например, даты (Главная – Формат ячейки – Другие числовые форматы - Дата). Переведите дату в тип ЧЧ.ММ.ГГГГ.
3. Двигайте столбцы и строки по ширине и высоте так, чтобы текст был виден. Обращаем внимание на то, что в MS Excel текстовые данные выравниваются по левому краю, а числа и даты – по правому краю.
4. Сохраните созданный вами документ.
5. Результат покажите учителю.
Самостоятельная работа:
Задание: Создайте расчетный лист по оплате ЖКХ в соответствии с образцом в MS Excel . В соответствующих ячейках введите текст и цифры. Отредактируйте введенные данные. Зафиксируйте данные в ячейке любым известным вам способом.
Возможности динамических (электронных) таблиц. Математическая обработка числовых данных, графическая обработка статистических таблиц в табличном процессоре MS Excel.
План
Структура окна MS Excel
Основные типы и форматы данных
Работа с книгами, листами и ячейками
Контрольные вопросы
Самый распространенный графический процессор на сегодняшний день является программа Ecxel, входящая в состав Microsoft Office и являющийся прикладным программным обеспечением.
Так же в состав пакета Microsoft Office входят программы:
MS Office Word 2007
MSOffice Access 2007
MS Office Outlook 2007
MS Office PowerPoint 2007
MSOffice Publisher 2007.
Microsoft Office Excel 2007 представляет собой, мощный табличный
процессор, который используется пользователями, для работы с электронными таблицами.
Табличный процессор (ТП) – комплекс программных средств для
математической, статистической и графической обработки текстовых и
числовых данных в табличном виде.
Электронная таблица – это информационная технология для профессиональной работы с данными, представляющая собой аналог обычной таблицы и позволяющая производить разнообразные вычисления с числовыми данными.
ОБЩИЕ СВЕДЕНИЯ ПО EXCEL
Программное средство Excel относится к классу систем обработки числовой информации, называемых SPREADSHEET, Буквальный перевод термина “spreadsheet” c английского языка на русский означает “РАССТЕЛЕННЫЙ ЛИСТ (бумаги)”. Между тем, в компьютерном мире под этим термином подразумевают совершенно определенный класс программных средств, именуемых у нас “ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ” или системами обработки числовой информации.
КРАТКИЙ ИСТОРИЧЕСКИЙ ЭКСКУРС
Первая электронная таблица VisiCalc была выпущена фирмой Visi Corporation в 1981 году, и именно с этого момента принято вести отсчет истории электронных таблиц как самостоятельного вида программного обеспечения.
Идея выделения таблиц в особый класс документов и создание специализированной программы, выполняющей всевозможные операции с табличными данными, оказалась весьма удачной и была подхвачена многими фирмами. Популярность электронных таблиц стремительно росла.
В 1983 году фирма Lotus Development Corporation выпустила электронную таблицу 1-2-3, ставшую на долгие годы фактическим стандартом в своей области.
В 1985 году появилась первая версия для платформы Macintosh наиболее распространенная сегодня Microsoft Excel. Спустя год данный сектор desktop-приложений пополнился пакетом Quattro, созданный компанией Borland International Corporation. В 1989 году он выходит под названием Quattro Pro.
Электронные таблицы сегодня занимают одно из лидирующих мест в структуре продаж делового программного обеспечения. Новое поколение ЭТ характеризуется новым уровнем функциональных возможностей. Помимо традиционных средств (таких как вычисления с использованием стандартных функций, автопересчет, объединение рабочих листов), современные пакеты ЭТ ориентированы на работу в среде Internet, дополнены средствами коллективной работы, значительно расширены функции по созданию деловой графики. Основными конкурентами среди производителей данного программного обеспечения в 1998 году являлись компании Corel (Quattro Pro 7), Microsoft (Excel 97, 7.0), Lotus Development (Lotus 1-2-3).
ОСНОВНЫЕ ВОЗМОЖНОСТИ ЭЛЕКТРОННЫХ ТАБЛИЦ
· · анализ и моделирование на основе выполнения вычислений и обработки данных;
· · оформление таблиц, отчетов;
· · форматирование содержащихся в таблице данных;
· · построение диаграмм требуемого вида;
· · создание и ведение баз данных с возможностью выбора записей по заданному критерию и сортировки по любому параметру;
· · перенесение (вставка) в таблицу информации из документов, созданных в других приложениях, работающих в среде Windows;
· · печать итогового документа целиком или частично;
· · организация взаимодействия в рабочей группе (коллективное использование, то есть распространение и просмотр электронных таблиц всеми участниками рабочей группы;
· · работа в Internet(поиск данных и публикация информации) с помощью инструментария ЭТ.
ПРЕИМУЩЕСТВА ИСПОЛЬЗОВАНИЯ ЭТ ПРИ РЕШЕНИИ ЗАДАЧ
1. 1. Решение задач с помощью электронных таблиц освобождает от составления алгоритма и отладки программы. Нужно только определенным образом записать в таблицу исходные данные и математические соотношения, входящие в модель.
2. 2. При использовании однотипных формул нет необходимости вводить их многократно, можно скопировать формулу в нужную ячейку. При этом произойдет автоматический пересчет относительных адресов, встречающихся в формуле. Если же необходимо, чтобы при копировании формулы ссылка на какую-то ячейку не изменилась, то существует возможность задания абсолютного (неизменяемого) адреса ячейки (автоматическая настройка ссылок).
CОСТАВ ЭЛЕКТРОННОЙ ТАБЛИЦЫ
В таблице используются столбцы (256) и строки (16384).
Строки пронумерованы от 1 до 16384, столбцы помечаются латинскими буквами от А до Z, и комбинациями букв АА, АВ. IV,
Элемент находящийся на пересечении столбца и строки назовем - ячейкой (клеткой).
Прямоугольная область таблицы называется блоком (диапазоном, интервалом) ячеек. Она задается адресами верхней левой и правой нижней ячеек блока, перечисленными через двоеточие.
МОДЕЛЬ ЯЧЕЙКИ В EXCEL
Интерфейс табличного процессора Microsoft Excel 2007
В окне Microsoft Excel 2007, как и в других программах MS Office 2007,
используется новый ленточный интерфейс (см.слайд).
При запуске Excel открывается окно приложения, в котором отображается новая рабочая книга – Книга 1. (рис.1)
Окно приложения Excel имеет 10 основных областей:
кнопка открытия диалогового окна групп;
строка названия ячеек;
область рабочего поля книги;
лента главного меню
Основная обработка данных в Excel осуществляется при помощи команд из строки меню.
Строка формул используется для ввода и редактирования значений, формул в ячейках или диаграммах.
Строка название ячейки – это окно слева от строки формул, в котором выводится имя активной ячейки.
Рабочая книга (документ Excel) состоит из рабочих листов.
Рабочие листы- листы, каждый из которых является электронной таблицей. По умолчанию открывается три рабочих листа или три электронных таблицы, переход к которым можно осуществить, щелкая на ярлычках, расположенных внизу книги. При необходимости в книгу можно добавить рабочие листы (электронные таблицы) или удалить их из книги.
Кнопка открытия диалогового окна групп- это маленькая кнопка в правой нижней части вкладки групп.
Область рабочего поля книги- это вся область рабочей книги на которой происходят действия по вычислению, построение диаграмм и т.д.
ЭТ состоит из клеток прямоугольных клеток- ячеек. Горизонтальные ряды клеток образуют – строки, вертикальные ряды- столбцы. В ячейки электронной таблицы можно вносить текст, числа, формулы.
К данным относятся: числа, даты, время суток, текст или символьные данные и формулы.
В ячейки электронной таблицы можно вносить текст, числа, формулы.
Текст – последовательность любых символов из компьютерного алфавита.
Формула – это выражение, определяющее вычисление действий ТП и математические вычисления. Для правильной записи формул нужно учитывать последовательность выполнения действий ТПроцессором.
Основное свойство ЭТ: изменение числового значения в ячейке приводит к мгновенному пересчету формул, содержащих имя этой ячейки.
Рабочая областьэлектронной таблицы состоит из строк и столбцов, имеющих свои имена. Имена строк – это их номера. Нумерация строк начинается с 1 и заканчивается максимальным числом, установленным для данной программы. Имена столбцов – это буквы латинского алфавита сначала от А до Z , затем от АА до AZ, ВА до BZ и т.д. в табличном процессоре Excel 256 столбцов и более 16 тысяч строк. (рис.2)
Ячейка– область, определяемая пересечением столбца и строки электронной таблицы, имеющая свой уникальный адрес.
Адрес ячейкиопределяется именем столбца и номером строки, на пересечении которых находится ячейка, например А10. Ссылка – указание адреса ячейки.
Активная ячейка- это выделенная ячейка, имя которой отображается в поле имени.
Маркером выделенияназывается полужирная рамка вокруг выделенной ячейки.
Маркер заполнения - это черный квадрат в правом нижнем углу выделенной ячейки.
Активная область листа- это область, которая содержит введенные данные.
В электронных таблицах можно работать как с отдельными ячейками, так и с группами ячеек, которые образуют блок.
Блок ячеек– группа смежных ячеек, определяемая с помощью адреса.
Адрес блока ячеекзадается указанием ссылок первой и последней его ячеек, между которыми ставится разделительный символ – двоеточие.
Перемещение и копирование формул
Ячейки с формулами можно перемещать и копировать. При перемещении формулы все ссылки (и абсолютные и относительные ), расположенные внутри формулы, не изменяются. При копировании формулы абсолютные ссылки не изменяются, а относительные ссылки изменяются согласно новому расположению ячейки с формулой.
Для быстрого копирования формул в соседние ячейки можно использовать средство автозаполнения.
Рис.1. 9. Пример автозаполнения формул
Смешанные ссылки
Ссылки на ячейки могут быть смешанными. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов имеет вид $A1 или $B1. Абсолютная ссылка строки имеет вид A$1, B$1. При изменении позиции ячейки, содержащей формулу, относительная часть ссылки изменяется, а абсолютная не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная часть ссылки автоматически корректируется, а абсолютная остается без изменений.
Рис. 1.6. Смешанная ссылка
Кроме понятия ячейки используется понятие диапазона – прямоугольной области, состоящей из нескольких (или одного) столбцов и нескольких (или одной) строк. В качестве адреса диапазона указываются адреса левой верхней и правой нижней ячеек диапазона, разделенные знаком двоеточие ( : ). Например, диапазон A1:C4 содержит 12 ячеек (по 3 ячейки в строках и 4 ячейки в столбцах).
Для работы с несколькими ячейками сразу необходимо выделить блок ячеек. Это выполняется следующим образом: для смежных ячеек щелкните на ячейке и удерживая кнопку мыши, протяните по листу указателем. При этом будет произведено выделение всех смежных ячеек. Блок описывается двумя адресами, разделенными знаком двоеточия - адресом верхней-левой и нижней-правой ячеек. На рисунке, например, выделен блок: A2:D4.
Рис. 1.7. Диапазон ячеек
В Excel можно выделять целые рабочие листы или их части, в том числе столбцы, строки и диапазоны (группы смежных или несмежных ячеек). Для выделения несмежных строк, столбцов или диапазонов необходимо нажать и удерживать в процессе выделения клавишу Ctrl.
Обязательная литература
Иванов В. Microsoft Office System 2003 Учебный курс. – СПб.: Питер; Киев: Издательская группа ВНV, 2004. – 640с.: ил. .
Относительные ссылки
Относительные ссылки на ячейки - это ссылки, значения которых изменяются при копировании относительно ячейки, куда будет помещена формула.
Например, ячейка B2 содержит формулу = B5+C8, т. е. первый операнд находится на три ячейки ниже в том же столбце, а второй операнд находится на 6 строк ниже и один столбец правее ячейки B2. При копировании данной формулы и вставке ее в ячейку С3, ссылки в ней снова будет указывать на ячейки, расположенные: первая - на три ячейки ниже в том же столбце, вторая - на 6 строк ниже и один столбец правее ячейки С3. Так, если формула из ячейки B2 копируется в ячейку С3, то формула примет вид =С6 + D9, а если ско-пировать содержимое В2 в B3, то в ячейке B3 формула примет вид = B6+C9.
Рис. 1.4. Относительная ссылка
Автозаполнение
Информация может вноситься в диапазон вручную или с использованием средства Автозаполнение, которое облегчает копирование данных из ячеек в соседние ячейки.
С помощью перетаскивания маркера заполнения ячейки её содержимое можно копировать в другие ячейки той же строки или того же столбца. Данные в Excel в основном копируются точно так же, как они были представлены в исходных ячейках.
Однако, если ячейка содержит число, дату или период времени, то при копировании с помощью средства Автозаполнение происходит приращение значения её содержимого. Например, если ячейка имеет значение «Январь», то существует возможность быстрого заполнения других ячеек строки или столбца значениями «Февраль», «Март» и так далее. Могут создаваться пользовательские списки автозаполнения для часто используемых значений, например, названий районов города или списка фамилий студентов группы.
Рис. 1.8. Пример автозаполнения по месяцам
В Excel разработан механизм ввода «рядов данных». Под рядами данных подразумеваются данные, отличающиеся друг от друга на фиксированный шаг. При этом данные не обязательно должны быть числовыми.
Для создания рядов данных необходимо выполнить следующие действия:
- введите в ячейку первый член ряда;
- подведите указатель мыши к черной точке в правом нижнем углу выделенной ячейки (в этот момент белый крестик переходит в черный) и нажмите на левую кнопку мыши;
- удерживая нажатой кнопку мыши, выделите нужную часть строки или столбца;
- после того как вы отпустите кнопку мыши, выделенная область заполнится данными.
Содержание теоретического минимума.
Рабочее окно, рабочие книги и рабочие листы.
Создание новой таблицы. Обрамление таблицы.
Выделение ячеек, их диапазонов, строк и столбцов.
Ввод данных через формы.
Копирование данных или формул в смежные ячейки.
Обработка числовых значений.
Функции в Microsoft Excel.
Ссылки на ячейки. Имена ячеек.
Рабочие листы Microsoft Excel.
Подготовка документа к печати.
Дополнительная литература
Ульрих Л.А. Электронные таблицы Microsoft Excel. Проблемы и решения: Практич. пособие / Пер. с англ. – Издательство ЭКОМ, 2002, 400 с.
Виллетт Эдвар, Кроулер Дэвид, Кроулер Ронда. Microsoft Office 2000 / Библия пользователя. Пер. с англ. – М.: Издательский дом «Вильямс». 2001. – 1026 с.
Левин А. Самоучитель работы на компьютере. Начинаем с Windows. 3-е изд. – СПб.: Питер, 2005. - 718с., ил.
Абсолютные ссылки
Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, используют абсолютные ссылки. Абсолютная ссылка всегда указывает на одну и ту же ячейку, независимо от расположения формулы, её содержащей. Для создания абсолютной ссылки на ячейку необходимо поставить знак доллара ($) перед той частью ссылки, которая не должна изменяться. Например, если в A1 находится формула =$B$5+$C$8, то при копировании содержимого ячейки A1 в ячейку В2 или A3 в этих ячейках также будетнаходиться формула =$B$5+$C$8, что говорит о том, что исходные данные всегда будут браться из ячеек В5 и С8.
Рис. 1.5. Абсолютная ссылка
Элементы экрана
Строка заголовка находится в верхней части экрана и имеет обычный вид для программ, работающих под управлением Windows, дополнительно здесь установлена кнопка Office , которая предназначена для вывода списка возможных действий с документом, включая открытие, сохранение и печать. Также на строке заголовка есть панель быстрого доступа.
Рис. 1.1. Структура рабочего листа
Ввод и редактирование данных
Для ввода информации в заданную ячейку нужно установить на нее курсор или нажать мышью на этой ячейке. После этого можно начинать вводить данные. С вводом первого символа вы переходите в режим ввода, при этом в строке формулы дублируется текстовая информация вводимая в ячейку а в строке состояния отображается слово Ввод.
Строка формул Microsoft Excel, используется для ввода или редактирования значений или формул в ячейках или диаграммах. Здесь выводится постоянное значение или формула активной ячейки. Для ввода данных выделите ячейку, введите данные и щелкните по кнопке с зеленой «галочкой» или нажмите ENTER. Данные появляются в строке формул по мере их набора.
Поле имени находится в левом конце строки ввода. Используйте поле имени для задания имен активной ячейке или выделенному блоку. Для этого щелкните на поле имени, введите туда имя и нажмите ENTER. Такие имена можно использовать при написании формул или при построении диаграмм. Также поле имени можно использовать для перехода к поименованной ячейке или блоку. Для этого откройте список и выберите из него нужное имя.
Рис. 1.3. Вид строки формул
Если информация уже введена в ячейку и нужно лишь добавить или скорректировать ранее введенные данные, дважды нажмите мышью на нужной ячейке или нажмите клавишу F2, когда требуемая ячейка выделена. При этом вы переходите в режим ввода и можете внести необходимые изменения в данные, которые находятся в этой ячейке.
Каждая ячейка имеет адрес, который обозначается именем столбца и именем строки. Например А2. Если надо задать адрес ячейки, которая находится на другом рабочем листе или в другой рабочей книге, перед именем ячейки задают имя рабочего листа, а во втором случае и имя рабочей книги. Например: Лист 1!А1 или =[Работа1]Задание1!$B$4 .
Наряду с понятием адреса ячейки в электронной таблице используется понятие ссылки. Ссылка– это элемент формулы, который используется тогда, когда надо сослаться на какую-нибудь ячейку таблицы. В этом случае адрес будет использоваться в качестве ссылки.
Есть два стиля представления ссылок в Microsoft Excel:
- Стиль ссылок R1C1 (здесь R — row (строка), C — column (столбец)).
Ссылки в Excel бывают 3-х видов:
- Относительные ссылки (пример:A1);
- Абсолютные ссылки (пример: $A$1);
- Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные, наполовину абсолютные).
Функции Excel
Функции Excel - это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления.
Excel имеет несколько сотен встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции являются эквивалентами длинных математических формул, которые можно сделать самому. А некоторые функции в виде формул реализовать невозможно.
Синтаксис функций
Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например СУММ, - описывает операцию, которую эта функция выполняет. Аргументы задают значения или ячейки, используемые функцией. В формуле, приведенной ниже: СУММ - имя функции; В1:В5 - аргумент. Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.
Знак равенства в начале формулы означает, что введена именно формула, а не текст. Если знак равенства будет отсутствовать, то Excel воспримет ввод просто как текст.
При использовании в функции нескольких аргументов они отделяются один от другого точкой с запятой .
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
311 лекций для учителей,
воспитателей и психологов
Получите свидетельство
о просмотре прямо сейчас!
Тема: Создание и редактирование входных/выходных форм документов с использованием табличного процессора EXCEL.
Цель проведения. В результате выполнения лабораторной работы студент должен иметь представление о функциональных возможностях табличного процессора EXCEL, приобрести навыки создания таблиц и форматирования ячеек.
Продолжительность - 4 часа.
Читайте также: