Лёзина Т. - MS Excel расширенные возможности
Ленинградский областной институт развития образования
Введение
Данное руководство представляет собой краткое изложение основных возможностей табличного процессора Microsoft Excel XP. Оно предназначено для пользователей, прослушавших соответствующий курс, и нуждающихся в справочнике по пакету Microsoft Excel XP.
Данное пособие содержит основные инструменты, необходимые пользователю в его повседневной деятельности. В конце пособия рассматриваются наиболее важные отличия версии ХР от версии Excel 2000.
Табличные процессоры (или электронные таблицы) относятся к наиболее простому типу инструментальных средств, позволяющих создавать приложения пользователя. Они предназначены для автоматизации любых расчетов и позволяют обрабатывать большие таблицы с выводом их на экран и печать.
Практически все расчеты, выполняемые в процессе управления, могут моделироваться с помощью табличных процессоров.
Создание таблиц в Excel
Ввод данных
Табличный процессор Excel позволяет вводить в каждую ячейку следующие типы данных:
1. текст - любую последовательность символов (данные текстового типа используются для заголовков таблиц, заголовков строк и столбцов, комментариев);
2. числовые константы: 5; 3,14;
3. формулы - выражения, начинающиеся со знака =, состоящие из числовых величин, адресов ячеек, функций и соединяющих их арифметических операций, например:
=А5*2/В1;
4. функции - запрограммированные в Excel стандартные формулы (например, функция суммирования имеет вид =СУММ(А1:А2)).
Для ввода данных в ячейку нужно щелкнуть на ней, ввести данные и нажать клавишу Enter.
В формулах используются следующие знаки арифметических операций:
сложение +
вычитание умножение деление
Для изменения порядка арифметических действий используются круглые скобки.
Редактирование содержимого ячейки производится в т.н. строке формул (См. рис.1). При ее отсутствии на экране следует выполнить команду Вид\Строка формул.
Рис.1. Строка редактирования Excel Форматирование элементов таблицы
Важной Excel различает форматирование всей ячейки и
' форматирование содержимого ячейки.
К форматированию ячеек относится: изменение шрифта содержимого ячеек, выравнивание данных в ячейках, представление чисел в разных форматах, оформление границ ячеек, и т.д. Для того чтобы изменить формат ячейки необходимо щелкнуть на ней и выполнить команду Формат\Ячейки.
Появившееся диалоговое окно Формат Ячеек (см. рис.2), позволит изменить формат всей ячейки.
Рис.2 . Диалоговое окно, позволяющее форматировать ячейку
- щелкнуть на форматируемой ячейке или выделить диапазон ячеек, содержащих выравниваемый текст;
- выполнить команду Формат\Ячейки. На закладке Выравнивание установить опцию Переносить по словам, которая позволяет располагать текст одной ячейки в несколько строк.
Для оформления предварительно выделенных ячеек с помощью рамок следует выполнить команду Формат\Ячейки. На закладке Граница следует выбрать тип линии для рамки, а затем щелкнуть на кнопке с нужным видом рамки. (Возможно поочередное назначение нескольких видов рамок).
Для назначения цветного фона предварительно выделенным ячейкам необходимо выполнить команду Формат\Ячейки. На закладке Вид выбрать цвет фона ячеек и нажать кнопку ОК.
Для изменения шрифта предварительно выделенного текста или числа, находящихся в нескольких ячейках, необходимо выполнить
команду Формат\Ячейки. На закладке Шрифт следует указать тип шрифта (Anal, Times New Roman и т. д.), начертание (жирный, курсив, подчеркнутый), размер шрифта, цвет и т.д.
Для изменения формата чисел, находящихся в выделенном диапазоне ячеек необходимо выполнить команду ФорматЯчейки. С помощью закладки Число можно установить один из следующих форматов чисел: денежный, процентный, дата, текстовый и т.д. (См. табл.1).
/center> |
||||||||||||||||||||||
| Табл. 1. Представление чисел в различных форматах |
Изменение высоты строки также производится с помощью мыши. Для этого нужно установить курсор на нижнюю границу ячейки серого цвета с цифрой - номером строки и, протянув мышь при нажатой левой кнопке мыши, установить требуемую высоту строки.
Для того чтобы вставить новую строку (новый столбец) следует установить курсор на той ячейке, перед которой будут вставляться строка (столбец) и выполнить команду: Вставка\Строки (Вставка\Столбцы). Для того чтобы удалить строку (столбец) следует:
1. Выделить строку (столбец), щелкнув для этого на номере строки (имени столбца).
2. Выполнить команду Правка\Удалить.
К форматированию содержимого ячеек относится только изменение шрифта содержимого ячейки. Для того чтобы изменить формат содержимого ячейки необходимо дважды щелкнуть на ней, выделить изменяемый фрагмент текста и выполнить команду Формат\Ячейки. В этом случае появится диалоговое окно, представленное на рис.3. В отличие от рис. 2, на нем присутствует всего одна закладка Шрифт. Указанные на этой закладке параметры - тип шрифта, его цвет, начертание, и т.д. будут применены только к заранее выделенному в ячейке фрагменту текста.
Рис. 3. Диалоговое окно Формат ячеек для форматирования фрагмента
текста в ячейке
Обработка формул в Excel
Как правило, в таблицах данные одного столбца рассчитываются в соответствии с одним и тем же алгоритмом. Поэтому для оптимизации ввода данных в таблицу Excel предлагает инструмент копирования формул.
Копирование формул
Копирование формул в Excel удобнее всего осуществлять с помощью мыши. Для этого:
1. Сначала введите формулу для расчета значения первого элемента столбца.
2. Установите курсор в правый нижний угол копируемой ячейки (курсор должен принять форму черного креста +).
3. Протащите мышь при нажатой левой кнопке мыши по диапазону, в который копируется формула.
При копировании формул относительные адреса Важно! ячеек автоматически пересчитываются. Для фиксации
ссылки на одну ячейку используются т.н. абсолютные адреса ячеек.
Относительный адрес ячейки - это обычный адрес ячейки: А12, В3. При копировании вниз формулы =А2*В2, в следующей ячейке появится формула =А3*В3, в следующей =А4*В4 и т.д.
Многие формулы требуют, чтобы при копировании один или несколько адресов ячеек не изменялись. В этом случае перед неменяющейся частью адреса следует поставить знак $. Адрес ячейки, содержащий знаки доллара, называется абсолютным адресом ячейки.
Примером абсолютных адресов являются $A$2, $C$12. В адресе A$1 неменяющейся частью адреса является номер строки 1. В адресе $A1 неменяющейся частью является имя столбца А.
Важно!
Быстро установить значки $ можно с помощью клавиши F4. Для этого щелкните в строке редактирования около адреса фиксируемой ячейки и нажмите на клавиатуре клавишу F4
ПРИМЕР 1. Расчеты в таблицах
Рассчитать величину начисленных денег и величину выдаваемой премии для каждого сотрудника отдела по формулам:
Начислено = Отработано часов*Тарифная ставка. Премия=Начислено *Процент премии.
Величина Процент премии - 10% - хранится в отдельной ячейке Е1. Формулы, по которым рассчитываются соответствующие таблицы, приведены ниже. (См табл.2)
Важно!
Для заполнения столбцов Начислено и Премия достаточно рассчитать значения только для сотрудника по фамилии Сидоров В.И. Для остальных сотрудников формулы нужно скопировать.
|
||||||||||||||||||||||||||||||||||||||||||||
| Табл. 2. Пример таблицы с расчетами |
|
|||||||||||||||||||||||||
| Табл. 3. Результаты вычислений по формулам, приведенным в табл. 2Ошибка! Источник ссылки не найден. |
ПРИМЕР 2. Расчеты в таблицах.
Рассчитать процент успеваемости и процент качества знаний учеников 5, 6, 7 классов.
Процент успеваемости=(отличники+ на 4 и 5+ троечники)/ кол-во учеников *100
Качество знаний = (отличники+ на 4 и 5)/кол-воучеников*100
Формулы, по которым рассчитываются соответствующие таблицы, приведены ниже. (См. табл. 4).
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 4 Пример таблицы с расчетами. |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 5. Результаты вычислений по формулам, приведенным в табл. 4 |
В ячейку Е7 введите формулу: =(E3+E4+E5)/$C$3*100;
в ячейку Е8 Е7 введите формулу: =(E3+E4)/$C$3*100;
Скопируйте формулы в ячейки F7, G7, H7 и в F8, G8, H8 соответственно.
Аналогично введите формулы для 10 б.(См. табл. бОшибка! Источник ссылки не найден.).
| ABC E F G И I I _ |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 6. Пример таблицы с расчетами Замечание: В результате ввода формул табл. 6 примет вид: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 7. Результаты вычислений по формулам, приведенным на табл. 6 Пример 3. Рассчитать коэффициент усвоения задания (К). |
| К= Средний балл за данное задание / максимальный балл за данное задание | |||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 8. Пример таблицы с расчетами |
| Замечание: В результате ввода формул таблица примет вид: | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 9. Результаты вычислений по формулам, приведенным в табл. 8 Использование функций Excel позволяет использовать большой набор функций, что облегчает расчеты в таблицах. |
1.
2.
3.
Установить курсор в той ячейке, где ожидается получить ответ. Вызвать Мастер фу'ЧІ'' ^Мастер функций можно вызвать
нажатием кнопки -^ ^-, находящейся рядом со строкой
редактирования, или командой Вставка\Функция.
В окне Категория появившегося диалогового окна (См.рис. 4) следует выбрать категорию функции (математическая, статистическая), затем в нижней части этого же диалогового окна щелкнуть на названии необходимой функции и нажать кнопку ОК.
Рис. 4 Окно Мастера функций
5. Нажать кнопку ОК.
ПРИМЕР 4. Расчет величины среднего количества отработанных сотрудниками часов
Для того чтобы рассчитать величину среднего количества отработанных сотрудниками часов следует щелкнуть в любой ячейке вне таблицы, например, в ячейке В8, вызвать Мастер функций, в категории Статистические выбрать функцию СРЗНАЧ и в появившемся диалоговом окне, в окошке Число 1 указать весь диапазон значений Отработано часов - В4:В7. Затем следует нажать кнопку ОК.
Если диалоговые окна заслоняют таблицу, то установите курсор на синюю полосу вверху окна и
Внимание!
при нажатой левой кнопке мыши переместите окно в удобное место экрана.
ПРИМЕР 5 Расчет значения премии сотрудникам с использованием функции ЕСЛИ
Изменим принцип начисления премии сотрудникам:
Если сотрудник отработал больше 20 часов, то назначим премиальный коэффициент равный 20%, в противном случае -10%. Предположим, что значение 10% хранится в ячейке Е1, а значение 20% хранится в ячейке Е2.
1. Для расчета премии первому сотруднику щелкните в ячейке Е4.
2. Вызовите Мастер функций.
3. В категории Логические выберите функцию ЕСЛИ.
4. В соответствующем диалоговом окне (См. рис. 5) следует указать:
- в окошке Лог_выражение: C420;
- в окошке Значение_если_истина: D4*$E$2;
- в окошке Значение_если_ложь: D4*$E$ 1.
5. Нажмите кнопку ОК.
Рис. 5. Диалоговое окно функции ЕСЛИ
В нашем примере результатом проверки является значение Истина.
В окошке Значение_если_истина указано то выражение, в соответствии с которым, будет произведен расчет премии в случае, если сотрудник отработал более 20 часов: Начислено*20%.
В окошке Значение_если_ложь указано то выражение, в соответствии с которым, будет произведен расчет премии в случае, если сотрудник отработал менее или 20 часов : Начислено*10%.
Значение 20% хранится в ячейке Е2.
Значение 10% хранится в ячейке Е1.
При расчете значений премии для каждого
Важно! сотрудника будет выполнено лишь одно из
вычислений: либо D4*$E$2, либо D4*$E$1.
Рис. 6. Диалоговое окно функции ЕСЛИ для расчета премии
следует зафиксировать ссылку на значения премиальных коэффициентов, хранящихся в ячейках Е1 и Е2.
На рис. 6 приведено диалоговое окно функции ЕСЛИ для рассмотренного примера.
ПРИМЕР 6 Анализ коэффициента усвоения с использованием функции ЕСЛИ.
Если К не менее 0,7, то процесс обучения можно считать удовлетворительным.
1. Для анализа коэффициента щелкните в ячейке В11.
2. Вызовите Мастер функций.
3. В категории Логические выберите функцию ЕСЛИ.
4. В соответствующем диалоговом окне (См. рис.7) следует указать:
- в окошке Лог_выражение: В10=0,7;
- в окошке Значение_если_истина: удовлетворительно;
- в окошке Значение_если_ложь: плохо;
5. Нажмите кнопку ОК.
В ячейку В11 введите формулу:
=ЕСЛИ(В10=0,7;Удовлетворительно;Плохо).
Скопируйте эту формулу в ячейки С11, D11.
В ячейку С11 введите формулу:
=ЕСЛИ(С10=0,7;Удовлетворительно;Плохо).
В ячейку D11 введите формулу:
=ЕСЛИф10=0,7;Удовлетворительно;Плохо) (см. рис. 7).
Рис. 7. Пример таблицы с расчетами.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
го о о |
Е | ||||
| 1 | Коэффициент усвоения задания | ||||
| 2 | Фамилия Имя | Задание 1 | Задание 2 | Задание 3 | |
| 3 | Иванов Максим | 8 | 5 | 8 | |
| 4 | Петров Алексей | 9 | 6 | 9 | |
| 5 | Сорокин Петр | 7 | 4 | 7 | |
| Б | Максимов Иван | 10 | 9 | 10 | |
| 7 | |||||
| 8 | Средний балл | 8,5 | 6 | 8,5 | |
| 9 | Максимальный балл |
10 | 9 | 10 | |
| 10 | Коэффициент усвоения |
0,85 | 0,67 | 0,85 | |
| 11 | Анализ коэффициента |
Удовлетвори тельно |
Плохо | Удовлетвори тельно |
7 Вложенные функции.
В расчетах часто приходится использовать т.н. вложенные функции.
ПРИМЕР 7. Начисление сложной премии.
Рассмотрим пример начисления премии по следующему алгоритму: Если отработано меньше или 10 часов, то премия не назначается. Если отработано больше или 20 часов, то назначается премия в размере 20% от Начислено.
В противном случае (отработано больше 10 часов и меньше 20 часов) назначается премия в размере 10% от Начислено.
Щелкните в ячейке Е4. Вызовите функцию ЕСЛИ.
Введите:
- в окошке Лог_выражение: C4=20;
- в окошке Значение_если_истина: D4*$E$2;
Щелкните в окошке Значение_если_ложь и вызовите вторую
функцию ЕСЛИ. Вложенные функции выбираются из списка, обведенного рамкой на рис. 9.
Рис. 9. Вставка вложенной функции ЕСЛИ
- в окошке Лог_выражение: C4=10;
- в окошке Значение_если_истина: D4;
- в окошке Значение_если_ложь: D4*$E$1. См. рис.
10.
Рис. 10. Диалоговое окно вложенной функции ЕСЛИ
Ученик выполнил 3 задания, за каждое задание он может получить максимально 10 баллов., т.е всего он может набрать 30 баллов. Установим соответствие между отметкой и количеством набранных баллов.
| балл | отметка |
| От 0 до 15 | 2 |
| От 16 до 21 | 3 |
| От 22 до 28 | 4 |
| 29, 30 | 5 |
Рис. 11. Алгоритм определения оценки
В ячейку F3, вставьте функцию ЕСЛИ.
Введите:
- в окошке Лог_выражение: Е316;
- в окошке Значение_если_истина: "отметка";
Щелкните в окошке Значение_если_ложь и вызовите вторую функцию ЕСЛИ. Вложенные функции выбираются из списка, обведенного рамкой на рис. 9.
Появится чистое диалоговое окно вложенной функции ЕСЛИ, в котором нужно ввести:
- в окошке Лог_выражение: Е322;
в окошке Значение если истина: "отметка 3"
в окошке Значение_если_ложь: вызовите третью функцию ЕСЛИ
- в окошке Лог_выражение: Е329;
- в окошке Значение если истина: "отметка 4";
- в окошке Значение_если_ложь: "отметка 5".
Скопируйте функцию из ячейки F3 в ячейки F4, F5, F6.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 10. Таблица с оценками учащихся Пример 9. Ведомость успеваемости 10 а класс за I полугодие. |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 11. Пример создания ведомости учащихся |
