Государственное бюджетное общеобразовательное учреждение Самарской области основная общеобразовательная школа №19 города Новокуйбышевск городского округа Новокуйбышевск
САЙТ ЗАКРЫТ!!!
НОВЫЙ САЙТ http://school19nsk.lbihost.ru/
Полезная информация | Июнь 18, 2012,15:11
Методические рекомендации для педагогов и учащихся образовательных учреждений
Основы работы с таблицами EXCEL
Уважаемые педагоги и учащиеся!
Предлагаем вашему вниманию методические рекомендации, в которых освещены основные приемы работы с табличным редактором EXCEL 2003. Microsoft Excel 2003 – табличный процессор, программа для создания и обработки электронных таблиц. Excel - мощный табличный редактор, позволяющий обрабатывать сложные табличные данные, но если воспользоваться встроенными макросами (VBA), то можно получить или создать приложение практически любой сложности и любого назначения.
Надеемся, что данная информация будет востребована вами при подготовке электронных материалов, которые требуют обработки данных в табличном редакторе или представления числовых данных в виде графиков и диаграмм, в том числе для включения их в электронные презентации или другие документы. Вы сможете продолжить обучение работе с программой при помощи методических пособий «Профессиональная работа с программой EXCEL 2007».
Желаем вам успехов в работе и достижения положительных результатов.
Надеемся на дальнейшее сотрудничество!
Содержание:
1. Основы работы в Excel __________________________________4
2. Работа с текстовыми данными ____________________________6
3. Автоматизация ввода данных ____________________________10
4. Ввод и форматирование числовых данных ________________13
5. Копирование и перемещение данных в ЭТ __________________17
6. Относительные и абсолютные ссылки _____________________21
7. Использование функций _________________________________25
9. Литература _____________________________________________30
1. Основы работы в Excel
Клавиши |
Перемещение |
, ¯, ¬, ® |
На одну ячейку в направлении, указанном стрелкой |
Ctrl + , |
В указанном направлении в ближайшую заполненную ячейку, если текущая ячейка оказалась пустой. Если текущая ячейка содержит данные, такая комбинация клавиш переместит вас в последнюю в указанном направлении заполненную ячейку. |
PgUp, PgDn |
Выше или ниже на один экран |
Home |
В крайнюю левую ячейку строки (столбец А) |
Ctrl + Home |
В левый верхний угол рабочей страница (ячейка А1) |
Ctrl + End |
В правый нижний угол рабочей области, которая содержит данные. |
Для удобства просмотра данных при перемещении по большому листу рабочей таблицы можно закрепить строки и столбцы с заголовками (чтобы они не перемещались). Для этого:
Иногда возникает необходимость скрыть рабочие книги, листы рабочих таблиц, отдельные столбцы и строки.
2. Работа с текстовыми данными
1) значение (константу) одного из четырех типов:
2) формулу, результатом которой будет являться значение одного из перечисленных выше типов или ошибочное значение.
Примечание. Для исправления непосредственно в ячейке должен быть установлен флажок Правка прямо в ячейке (Сервис/Параметры/вкладка Правка)
Примечание. Чтобы быстро вставить один или несколько столбцов или строк выделите нужное количество столбцов или строк, щелкните на них правой кнопкой мыши и в появившемся контекстном меню выберите пункт Вставить.
Чтобы установить свойства ячейке или группе ячеек, необходимо выделить эту ячейку (группу) и выбрать в меню команду Формат/Ячейки или нажать клавиши быстрого вызова Ctrl-1.
Чтобы отменить ранее установленные свойства ячейки необходимо выполнить команду верхнего меню Правка/Очистить/Форматы.
Чтобы удалить содержимое и отменить ранее установленные свойства, нужно выполнить команду меню Правка/Очистить/Все
Создайте таблицу (порядок создания описан ниже):
|
|
A |
B |
C |
D |
|
1 |
Вид изделия |
Название изделия |
Цена |
Стоимость |
|
2 |
|
|
|
|
|
3 |
|
|
|
|
1) Выделите диапазон ячеек A1:D1 (при выделении в первой клетке диапазона фон не изменяется)
2) Выполните команду Формат /Ячейки. На экране появится диалоговое окно Формат ячеек. Щелкните на вкладке Выравнивание. В данной вкладке выберите нужные опции:
3) Измените шрифт в ячейках A1:D1, установив Arial, полужирный, 12пт (вкладка Шрифт).
4) Измените ширину столбцов так, чтобы таблица выглядела как заданная (см. Общие понятия п.5).
5) Создайте обрамление таблицы (команда Формат/Ячейки/Вкладка Границы), предварительно выделив диапазон ячеек А1:D3.
10. Добавьте к заголовку еще одну строку с текстом: кондитерской фирмы "Сладкоежка", расположите его также по центру таблицы.
11. Добавьте еще одну пустую строку для отделения заголовка от таблицы (ячейки этой строки можно не объединять)
12. Введите текстовые значения в ячейки и выполните обрамление так, чтобы получилась таблица:
|
A |
B |
C |
D |
E |
1 |
Список товаров |
||||
2 |
кондитерской фирмы "Сладкоежка" |
||||
3 |
|
|
|
|
|
4 |
Вид изделия |
Название изделия |
Количество упаковок |
Цена |
Стоимость в рублях |
5 |
Шоколад |
Аленка |
|
|
|
6 |
Конфеты |
Весна |
|
|
|
7 |
Карамель |
Чебурашка |
|
|
|
8 |
Карамель |
Гусиные лапки |
|
|
|
9 |
Конфеты |
Мишка косолапый |
|
|
|
10 |
Конфеты |
Каракум |
|
|
|
11 |
Итого |
|
|
|
|
3. Автоматизация ввода данных
Для ввода данных в интервал ячеек нужно его выделить, а затем последовательно вводить данные в ячейки этого интервала. При этом, если после ввода очередного значения вы будете нажимать клавишу ENTER, данные будут вводиться по столбцам, а если ТАВ, то – по строкам (независимо от установленного направления перехода на вкладке Правка в окне Параметры).
Для ввода одинаковых данных в диапазон ячеек нужно его выделить, затем набрать на клавиатуре требуемое значение, нажать клавишу CTRL, и не опуская ее, ENTER. Весь диапазон заполнится одинаковыми данными. Так же можно заполнять несмежные диапазоны.
Выделение несмежных ячеек и диапазонов осуществляется так: выделяется первый (любой) диапазон, затем нажимается клавиша CTRL. И уже принажатой клавише CTRL выделяются остальные диапазоны.
Задание 2.
Заполнение и создание рядов с помощью маркера заполнения
Под рядами данных подразумеваются данные, отличающиеся друг от друга на фиксированный шаг. При этом данные не обязательно должны быть числовыми.
Маркер заполнения. Маркер заполнения – это небольшой прямоугольник в правом нижнем углу рамки выделения ячейки. Если он отсутствует, то необходимо на вкладке Правка (Сервис/Параметры) установить флажок Перетаскивание ячеек. Маркер заполнения используется для заполнения смежных ячеек одинаковыми данными и создания рядов данных.
Заполнение смежных ячеек одинаковыми данными (копирование данных в смежные ячейки)
10. Заполните блок клеток А13:С23 нулями с помощью маркера заполнения. Для этого в А13 введите 0 и «растяните» это значение на диапазон А13:А23, а затем, не снимая выделения, опять с использованием маркера заполнения «растяните» весь диапазон вправо на столбцы В и С.
Использование списков для автозаполнения.
11. Перейдите на Лист3.
12. В ячейку А2 введите текст: Среда. Растяните это значение на 7-10 ячеек вниз. Вместо ожидаемого копирования произошло заполнение ячеек последовательными днями недели.
Эта операция называется Автозаполнение. Стандартные списки для автозаполнения можно просмотреть на вкладке Списки окна Параметры (Сервис- Параметры). Используя это окно можно создать собственный список.
13. Создайте собственный список из 5-7 значений, содержащий, например, цвета радуги, названия деревьев и т.д. Для этого значения списка нужно заранее ввести в диапазон ячеек (строки или столбца).
Обычно при создании ряда маркер заполнения перетаскивается вниз или вправо, и при этом значения при создании рядов данных возрастают. При перетаскивании маркера вверх или влево, создается ряд с убывающими значениями в этих направлениях.
Общие правила создания рядов при перетаскивании маркера заполнения вниз или вправо заключаются в следующем:
Можно сделать вывод:
При выделении ячейки (диапазона) и перетаскивании маркера заполнения в любом направлении, содержимое этой ячейки (диапазона) либо копируется в направлении перетаскивания указателя, либо создается ряд данных. Это зависит от содержимого ячеек, формы выделения и использования клавиши Ctrl.
14. Перейдите на лист Задания и выполните приведенные в нем задания.
15. Сохраните и закройте файл ЛР3_Ряды.xls.
Задание 3.
Ознакомьтесь с функциями команды Правка/Заполнить/Прогрессия. Подумайте, какие из них нельзя выполнить с помощью маркера заполнения.
4. Ввод и форматирование числовых данных
Обычно, если вы явно не измените формат ячейки, Excel отобразит введенное числовое (и текстовое) значение в формате Общий. За некоторыми исключениями, формат Общий отображает точно то, что введено в ячейку. Исключения:
13. Длинные десятичные значения округляются или отображаются в экспоненциальной форме. Например, при вводе в ячейку со стандартной шириной целого числа 12345678901234 формат Общий представит его как 1,23Е+13, а 123456,7890123 - как 123456,8
14. Не выводятся незначащие нули. Например, 123,0 выводится как 123.
15. Десятичная дробь, введенная без числа слева от десятичной запятой, выводится с нулем. Например, ,123 выводится как 0,123.
Категория Числовой имеет параметры, позволяющие выводить числовые значения в виде целых чисел или чисел с фиксированной запятой, а так же выделять отрицательные значения с помощью цвета. В формате нужно задать число выводимых десятичных знаков, можно установить флажок Разделитель групп разрядов (при этом будут добавлены пробелы между сотнями и тысячами, тысячами и миллионами и т.д.).
Аналогичны форматам в категории Числовой за исключением того, что вместо управления выводом пробела можно управлять выводом денежной единицы.
Эти форматы аналогичны денежным и предназначены для составления различных финансовых документов
Используется для вывода числа в виде процентов. Внимание! В этом формате значение ячейки умножается на 100 и выводится на экран с символом %. Например, процентный формат без десятичных знаков число 0,1234 выведет как 12%, а при задании двух десятичных знаков - как 12,34%.
В этой категории форматов дробные значения выводятся как фактические дроби, а не в виде десятичных значений. Например, в зависимости от выбранного типа формата по количеству цифр в числителе и знаменателе или с указанием определенного знаменателя число 123,456 будет представлено как 123 1/2 или 123 26/57 или 123 7/16 и т.д.
Выводит число в экспоненциальной форме. Например, такой формат с двумя десятичными знаками выведет значение 98765432198 как 9,88Е+10. Увеличение числа десятичных знаков позволяет увеличить точность вывода. Экспоненциальные форматы используются обычно для вывода очень больших или очень малых чисел.
Применение текстового формата к ячейке означает, что значение в этой ячейке должно трактоваться как текст. Практически во всех случаях числовая константа, форматированная как текст, может быть использована в Excel как числовое значение.
Используются для ввода и хранения данных типа номеров телефона, почтовых индексов, табельных номеров и т.п.
à выделить ячейку или диапазон ячеек, числовые значения которых нужно отформатировать,
à выполнить команду Сервис-Формат ячеек, выбрать вкладку Число ,
à в списке Числовые форматы выбрать необходимый числовой формат, при этом в поле Образец будет показано число в данном формате,
à задать некоторые параметры данного формата, если вас не устраивают установленные по умолчанию (например, изменить число десятичных знаков после запятой).
Столбец |
Формат |
Параметры |
Столбец |
Формат |
Параметры |
В |
Числовой |
Число десятичных знаков - 3 |
H |
Процентный |
С точностью до целых |
С |
Числовой |
Отрицательные числа – красные без знака |
I |
Дробный |
По умолчанию (Простыми дробями) |
D |
Числовой |
Разделитель груп разрядов |
J |
Дробный |
Дробями до двух цифр |
E |
Денежный |
Число десятичных знаков - 1 |
K |
Дробный |
Шестнадцатыми долями |
F |
Денежный |
Обозначение – Евро |
L |
Экспоненциальный |
Число знаков - 1 |
G |
Процентный |
Все по умолчанию |
M |
Экспоненциальный |
Число знаков - 4 |
16. В верхней строке каждого столбца укажите примененный формат.
17. Сохраните, но не закрывайте текущую книгу.
18. Перейдите на лист Задания.
19. Выполните задания, приведенные на этом листе.
20. Сохраните и закройте файл ЛР5_Числа.xls
5. Копирование и перемещение данных в ЭТ
Копировать и перемещать содержимое ячеек можно двумя способами:
1) с помощью команд меню Вырезать, Копировать, Вставить (или соответствующих кнопок на ПИ, или быстрых клавиш Ctrl-X, Ctrl-C, Ctrl-V)
2) перетаскивая их мышью (метод "Drag-and-Drop")
Копируемые или перемещаемые ячейки можно:
1) вставить поверх существующих, при этом происходит замещение содержимого существующих ячеек.
2) вставить между существующих ячеек, при этом в ЭТ существующие ячейки сдвигаются вправо или вниз
Подготовка к работе.
Создайте новую таблицу с именем LR04.xls.
Для наглядности вставки или замещения данных, заполните нулями диапазон С1:Н16 нулями. Для этого выделите этот диапазон, затем на клавиатуре наберите цифру 0, нажмите клавишу Ctrl и, удерживая ее, нажмите Enter.
Копирование одной ячейки
В ячейку А4 занесите число 123,45
Во всех выполненных командах была осуществлена вставка поверх содержимого существующих ячеек. Такой вставке соответствует команда меню Правка-Вставка, кнопка Вставка на ПИ, быстрые клавиши Ctrl-V, в контекстном меню команда Вставить.
Внимание! Если не оговорено специально, то в дальнейших заданиях подразумевается именно такая вставка
Копирование диапазонов
Различные варианты вставки скопированных ячеек
Различные варианты вставки перемещаемых ячеек
Команды Вставка/Скопированные (Вырезанные) ячейки выполняют вставку между существующих ячеек со сдвигом ячеек вправо или вниз. В контекстном меню им соответствуют команды Добавить скопированные (вырезанные) ячейки.
6. Относительные и абсолютные ссылки
Если при изменении положения формулы (при копировании и распространении) автоматически меняются ссылки на ячейки относительно исходной, то такие ссылки называются относительными.
Воспользуйтесь справочной системой MS Excel и найдите определение относительной ссылки. Сравните приведенное ниже определение с найденным вами.
Относительная ссылка используется в формуле для указания адреса ячейки, вычисляемого в относительной системе координат с началом в текущей ячейке.
Относительные ссылки имеют следующий вид: А1, В1 и т.п..
Задание 2
Подготовьте таблицу по образцу, поместив ее в ячейках A1 – E3.
В ячейке В3 находится цена за единицу товара.
В ячейке C3 - формула =B3 * C2 (цена за единицу товара умножить на количество).
|
A |
B |
C |
D |
E |
1 |
Наименование товара |
Количество |
|||
2 |
1 |
2 |
3 |
4 |
|
3 |
Мороженное |
12,70 |
=B3 * C2 |
|
|
С помощью маркера заполнения распространите формулу вправо для получения стоимости товара за 3 и 4 единицы. Сравните свой результат с приведенным ниже.
|
A |
B |
C |
D |
E |
1 |
Наименование товара |
Количество |
|||
2 |
1 |
2 |
3 |
4 |
|
3 |
Мороженное |
12,70 |
25,40 |
76,20 |
304,80 |
Можно заметить, что вычисленная по формуле стоимость товара за три единицы неверна. Если выделить ячейку D3, то в Строке формул появится формула C3*D2, а должна быть формула B3*D2.
В результате распространения формулы вправо изменились и ссылки. А в нашем примере необходимо было каждый раз количество товара умножать на цену за единицу, то есть на содержимое ячейки B3.
В таких случаях, составляя формулу, применяют абсолютные ссылки. При перемещении или копировании формулы абсолютные ссылки не изменяются, ячейка фиксируется. В то время как относительные ссылки, с которыми мы работали до сих пор, автоматически обновляются в зависимости от нового положения.
Абсолютные ссылки имеют вид: $F$9; $C$45. Для фиксации координат применяется знак $.
Следовательно, для того, чтобы получить верные результаты в нашем примере, в ячейке C3 - формула =$B$3 * C2.
Измените эту формулу и распространите её вправо.
Задание 3
С помощью справочной системы найдите определение абсолютной ссылки. Сравните приведенное ниже определение с найденным вами.
Абсолютная ссылка используется для указания адреса ячейки, вычисляемого в абсолютной системе координат и не зависящего от текущей ячейки.
Абсолютные ссылки имеют вид: $А$5, $F$5, $G$3 и т.п.
Примечание
Для того, чтобы относительную ссылку преобразовать в абсолютную, достаточно после ввода ссылки нажать клавишу F4 – и знаки доллара появятся автоматически.
Задание 4
Подготовьте таблицу для начисления пени в соответствии с образцом.
|
||||
Оплата коммунальных услуг задержана на |
|
дней |
||
|
||||
Вид оплаты |
Начисленная сумма |
Пени |
Всего к оплате |
|
Квартплата |
|
|
|
|
Газ |
|
|
|
|
Электричество |
|
|
|
|
Телефон |
|
|
|
|
|
Пени высчитывается по формуле – 1% от начисленной суммы за каждый задержанный день.
Всего к оплате считается как сумма начисления плюс пени.
Добавьте в таблицу строку для подсчета итоговых показателей:
всего начислено, всего пени, всего к оплате.
Задание 5
Представьте себя одним из организаторов игры «Угадай мелодию». Вам поручено отслеживать количество очков, набранных каждым игроком, и вычислять суммарный заработок в рублях в соответствии с текущим курсом валюты.
Заготовьте таблицу по образцу.
Курс валюты |
|
|
||
|
||||
|
1 раунд |
2 раунд |
3 раунд |
Суммарный заработок за игру |
1-й участник |
|
|
|
|
2-й участник |
|
|
|
|
3-й участник |
|
|
|
|
|
Формулу для определения суммарного заработка пишите только для первого игрока, а остальным её распространяете.
Часто применяют не чисто относительные или абсолютные ссылки, а смешанные ссылки, например, С$1 или $C1.
Часть ссылки, не содержащая знак $, будет обновляться при копировании, а другая часть, со знаком $, останется без изменения.
В первом случае будет фиксироваться положение строки, а во втором случае – положение столбца.
Для смешанных нет необходимости вводить знак доллара с клавиатуры, достаточно воспользоваться клавишей F4.
Многократное нажатие клавиши F4 работает, как переключатель и позволяет выбрать нужную ссылку.
Задание 6
Составьте таблицу сложения чисел первого десятка.
Таблица сложения |
||||||||||
|
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
0 |
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|