Лёзина Т. - 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).
|
||||||||||||||||||||||
| Табл. 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. Пример создания ведомости учащихся |
Использование имен ячеек и диапазонов в формулах
В ячейке F3 Найдем количество пятерок для первого ученика. Вызовем Мастер функций, в списке Категория выберем Статистические. В списке функций выберем функцию СЧЕТЕСЛИ.В поле Диапазон введем В3:Е3, в поле Критерий "5".
После нажатия на кнопку OK, в строке формул отобразится формула =СЧЁТЕСЛИ(В3:Е3;5), а в ячейке F3 будет находиться количество пятерок.
В ячейке F4 найдем количество пятерок для второго ученика по формуле =СЧЁТЕСЛИ(В4:Е4;5)
Аналогично найдем количество пятерок для третьего и четвертого учеников.
В ячейке G3 найдем количество четверок для первого ученика по формуле: =СЧЁТЕСЛИ(В3:Е3;4). Аналогично найдем количество четверок для второго , третьего и четвертого учеников.
В ячейке H3 найдем количество троек для первого ученика по формуле: =СЧЁТЕСЛИ(В3:Е3;3). Аналогично найдем количество троек для второго , третьего и четвертого учеников.
В ячейке I3 найдем количество двоек для первого ученика по формуле: =СЧЁТЕСЛИ(В3:Е3;2). Аналогично найдем количество двоек для второго , третьего и четвертого учеников.
В ячейку J3, вставим функцию ЕСЛИ.
Введите:
- в окошке Лог_выражение: I30;
- в окошке Значение_если_истина: неуспевающий;
Щелкните в окошке Значение_если_ложь и вызовите вложенную функцию ЕСЛИ.
Появится чистое диалоговое окно вложенной функции ЕСЛИ, в котором нужно ввести:
- в окошке Лог_выражение: H30;
- в окошке Значение_если_истина: с 3;
- в окошке Значение_если_ложь: вызовите третью функцию ЕСЛИ;
- в окошке Лог_выражение: G30;
- в окошке Значение_если_истина: на 4 и 5;
- в окошке Значение_если_ложь: отличник;
Скопируйте функцию из ячейки Л3в ячейки J4, J5, J6.
В ячейке J3 будет находится формула:
=ЕСЛИ(І30;неуспевающий;ЕСЛИ(Н30;с 3;ЕСЛИ^30;на 4
и 5;отличник)))
В ячейке J7 подсчитаем количество отличников по формуле:
=СЧЁТЕСЛИд3^6;отличник).
В ячейке J8 подсчитаем количество хорошистов по формуле:
=СЧЁТЕСЛИд3^6;на 4 и 5).
В ячейке J9 подсчитаем количество троечников по формуле:
=СЧЁТЕСЛИд3^6;с 3).
В ячейке J10 подсчитаем количество неуспевающих по формуле:
=СЧЁТЕСЛИ^3^6;неуспевающий).
Использование имен ячеек и диапазонов в формулах.
При организации расчетов очень удобно использовать заранее определенные имена ячеек и диапазонов ячеек.Для того чтобы присвоить имя ячейке или диапазону ячеек следует:
1. Выделить ячейку или диапазон ячеек.
2. Выполнить команду меню Вставка/Имя/Присвоить.
3. Ввести имя создаваемого блока ячеек в строке Имя (автоматически
вводится имя, соответствующее содержимому ячейки,
расположенной над выделенным блоком), в строке Формула выводится ссылка на диапазон выделенных ячеек. Нажать кнопку
ОК.
Имя ячейки или диапазона не может содержать Важно! пробелы. Вместо пробела можно использовать символ
нижнего подчеркивания.
В дальнейшем в формулах можно использовать имена Выиматие' вместо абсолютных адресов ячеек и диапазонов. Для
вызова списка именованных диапазонов следует нажать клавишу F3.
Примеры использования имен в формулах.
ПРИМЕР 10. Расчет премии сотрудникам отдела с использованием имени ячейки
Рассмотрим расчет премии из примера 1.
1. Присвоим ячейке Е1 со значением премии имя Премия. Для этого:
- щелкните на ячейке Е1;
- выполните команду Вставка\Имя\Присвоить;
- в появившемся диалоговом окне (См.рис.12) в окошке Имя введите Премия;
- нажмите кнопку ОК.
2. При расчете величины премии для Сидорова В.И. щелкните в соответствующей ячейке, введите =D4*, затем, вместо ввода значения процента премии, нажмите на клавиатуре клавишу F3.
В появившемся диалоговом окне (См. рис. 13) щелкните на имени Премия.
Нажмите кнопку ОК.
Рис. 12. Диалоговое окно Присвоение имени
Формула примет вид: Б4*Премия. Скопируйте формулу для расчета премии остальным сотрудникам.
Именованная ячейка играет в формулах роль
Важно!
абсолютной ссылки.
Рис. 13. Диалоговое окно Вставка имени
ПРИМЕР 11. Пример расчета среднего значения премии сотрудников отдела с использованием именованного диапазона.
Рассмотрим пример расчета среднего значения премии сотрудников отдела.1. Выделите диапазон с премиями сотрудников отдела.
2. Присвойте этому диапазону имя Премии_отдела. (Для этого выполните команду Вставка\Имя\ Присвоить.)
3. Щелкните в любой ячейке вне таблицы.
4.
5.
6.
7.
8.
Вызовите Мастер функций, нажав кнопку L_
В категории Статистические щелкните на функции СРЗНАЧ, нажмите кнопку ОК.
Проследите, чтобы в появившемся диалоговом окне (См. рис. 14) курсор находился в окне Число 1.
Нажмите на клавиатуре клавишу F3 и в появившемся списке именованных диапазонов дважды щелкните на имени Премии_отдела.
Нажмите кнопку ОК.
В результате этих действий будет рассчитано среднее значение премий сотрудников отдела. Формула расчета будет выглядеть следующим образом: СРЗНАЧ(Премии_отдела).
Рис. 14. Диалоговое окно функции СРЗНАЧ
Для того чтобы присвоить имя листу установите курсор на имени листа (Листі) и нажмите правую кнопку мыши. В появившемся контекстном меню выберите команду Переименовать. Поверх старого имени листа введите новое имя.
Нажмите клавишу Enter.
Создание диаграмм в Excel.
Для создания диаграммы на основе данных таблицы необходимо:
1.
2. 3.
на панели
Вызвать Мастер диаграмм, нажатием кнопки инструментов Стандартная или командой Вставка\Диаграмма.
В появившемся диалоговом окне выбрать тип и вид диаграммы. Нажать кнопку Далее.
Во втором диалоговом окне:
- На вкладке Диапазон данных задать диапазон данных таблицы, подлежащий отображению на диаграмме, и указать вариант представления данных: по строкам или столбцам. (Для облегчения дальнейших настроек мы рекомендуем выделять данные, включая подписи строк и заголовки столбцов).
Несмежные диапазоны данных выделяются при нажатой клавише Ctrl.
Важно!
- На вкладке Ряд в окне Имя следует задать адрес ячейки, из которой формируется легенда для выделенного в окошке Ряд ряда данных. На этой же вкладке в окошке Подписи по оси Х можно задать адреса ячеек, из которых формируются подписи по оси Х.
Нажать кнопку Далее.
Ряд данных - это блок ячеек, содержащий данные, отражающиеся на диаграмме в виде отдельного Важно! графика.
Легенда - это подпись, определяющая соответствие цвета и ряда данных.
4. В третьем диалоговом окне следует задать параметры для оформления диаграммы. Нажать кнопку Далее.
5. В четвертом диалоговом окне следует указать, где будет размещаться диаграмма - на отдельном листе или на том же, что и таблица. Нажать кнопку Готово.
Диаграмма готова. Далее можно отредактировать диаграмму.
К редактированию диаграмм относится: изменение цвета ряда данных, формирование подписей данных рядов данных или их отдельных точек, изменение цвета области данных и т.д.
При редактировании диаграмм рекомендуется соблюдать общую технологию:
1. Щелчком левой кнопки мыши выделить подлежащий редактированию объект диаграммы (область диаграммы, область построения, ряд данных, точку данных, легенду, ось Х или У и т.д.)
2. Щелчком правой кнопки мыши на выделенном объекте вызвать контекстное меню и выбрать из него необходимую команду.
Например:
- для изменения типа предварительно выделенного ряда данных в соответствующем контекстном меню следует выбрать команду Тип диаграммы;
- Для изменения цвета предварительно выделенного ряда данных в соответствующем контекстном меню следует выбрать команду Формат рядов данных. На закладке Вид указать цвет ряда данных.
- Для настройки ряда на вспомогательную ось в соответствующем контекстном меню следует выбрать команду Формат рядов данных. На закладке Ось выбрать опцию по вспомогательной оси.
- Для организации подписи отдельной точки на диаграмме (выделить точку можно в два этапа: первым щелчком мыши выделяется ряд данных, а вторым щелчком - нужная точка) в соответствующем контекстном меню следует выбрать команду Формат точки данных. На закладке Подписи данных выбрать опцию значение.
Часто возникает необходимость добавить данные на уже построенную диаграмму.
Для добавления новых данных_на диаграмму необходимо:
1. выделить всю диаграмму (для этого достаточно щелкнуть один раз на
диаграмме);
2. выполнить команду ДиаграммаЩобавить данные;
3. в появившемся окне задать диапазон ячеек с добавляемыми данными.
Иногда Excel просит уточнить, являются ли новые данные значениями для нового ряда или они являются новыми элементами существующих рядов данных.
ПРИМЕР 12. Пример построения диаграммы.
| Построим диаграмму по следующей таблице: | |||||||||||||||||||||
|
|||||||||||||||||||||
| Табл. 13 Таблица начислений заработной платы сотрудникам отдела |
1. Для построения диаграммы запустите Мастер диаграмм.
2. В первом диалоговом окне укажите тип диаграммы - График. Нажмите кнопку Далее.
3. В следующем диалоговом окне щелкните в окошке Диапазон и выделите всю таблицу, включая заголовки столбцов и фамилии сотрудников.
4. Щелкните на опции Ряды в строках. В этом случае рядом данных будет строка таблицы.
5. Щелкните на закладке Ряд.
6. Проконтролируйте, чтобы в окне Подписи по оси Х был указан диапазон с названиями месяцев.
7. Обратите внимание, что в окошке Имя указана ячейка с фамилией сотрудника, соответствующая выделенному ряду. Нажмите кнопку Далее.
8. В следующем диалоговом окне укажите заголовок диаграммы Заработная плата за 1 квартал, и название оси У: Начислено в руб.. Нажмите кнопку ОК.
9. В следующем окне укажите месторасположение диаграммы. Нажмите кнопку ОК.
Типы диаграмм в Excel.
Обычно тип диаграммы выбирается в зависимости от того, что
именно надо продемонстрировать на диаграмме.
Гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных.
Диагр. 2. Гистограмма
Линейчатая диаграмма отражает взаимное соотношение отдельных компонентов таблицы. Линейчатая диаграмма представлена на Диагр. 3. Круговая диаграмма характеризует как абсолютную величину каждого элемента ряда данных, так и его вклад в общую сумму.
Диагр. 5. Диаграмма типа График Успеваемость
Диагр. 6. Гистограмма
Отличники
Диагр. 7. Круговая диаграмма
Анализ данных в Excel
Excel имеет ряд инструментов для удобного анализа данных таблиц. К ним относятся сортировка данных, фильтрация данных, сводные таблицы и т. д.
Сортировка данных.
1. Для успешной сортировки данных в Excel рекомендуется проверить, как организованы данные в сортируемой таблице:
- если заголовок каждого из столбцов размещен строго в одной ячейке, расположенной непосредственно над столбцом, то такая таблица может быть отсортирована без предварительной подготовки;
- если заголовок каждого из столбцов размещен в нескольких ячейках, то следует перед первой строкой данных в таблице вставить строку с условными (вспомогательными) заголовками.
2. Для сортировки данных необходимо выделить весь сортируемый диапазон, включая заголовки столбцов (если была вставлена строка с условными заголовками, то выделяется именно она), и выполнить команду Данные\Сортировка. В появившемся диалоговом окне следует указать, по каким полям осуществляется сортировка таблицы.
ПРИМЕР 14. Пример сортировки таблицы.
Приведенная таблица (См. табл.14) может быть отсортирована сначала по виду Продукции, а затем по фамилии Продавца.
Такая сортировка означает, что каждая группа записей с одинаковым видом Продукции будет отсортирована, в свою очередь, по фамилии Продавца.
Для сортировки таблицы нужно
1. выделить таблицу, включая заголовки ее столбцов, и выполнить команду Данные\ Сортировка;
2. в появившемся диалоговом окне (См.рис. 15) в поле Сортировать по следует указать первое поле сортировки - Продукция;
3. в поле Затем по нужно указать Продавец. Нажмите кнопку ОК.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл.14. Фрагмент таблицы, подлежащей сортировке |
| ^^__^^едомостъ^спеваемости10_а_класса_заЛполугодие.і | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Рис. 15. Диалоговое окно Сортировка диапазона ПРИМЕР 15. Пример сортировки таблицы.
Список учеников
Приведенная таблица (См. табл. 15) может быть отсортирована сначала по Результату, а затем по фамилии ученика.| После сортировки таблица примет вид. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Табл. 16. Отсортированный список учеников |
Одним из простых инструментов анализа данных является инструмент Промежуточные итоги.
Если требуется определить итоговую выручку, полученную каждым продавцом, то следует:
1. Отсортировать таблицу по полю Продавец.
2. Выполнить команду Данные\Промежуточные итоги.
3. В появившемся диалоговом окне (см. рис. 16) нужно указать:
- При каждом изменении в - Продавец;
- Операция - Сумма;
- Добавить итоги по Выручка в руб..
Поскольку после предварительной сортировки таблицы, записи с одинаковыми фамилиями продавцов стоят рядом, результат суммирования выручки для каждого продавца появится перед группой записей с фамилией другого продавца. (См. рис. 17).
В качестве функций анализа в Промежуточных итогах могут быть использованы функции Среднее, Максимум, Минимум, Количество и др.
Если для дальнейшего анализа требуется подвести ВажНО! промежуточные итоги по каждому из указанных
районов, то следует предварительно отсортировать таблицу по соответствующему полю.
Рис. 16. Диалоговое окно Промежуточные итоги
Рис. 17. Результат расчета Промежуточных итогов
Очень часто при анализе данных в таблице возникает необходимость анализировать только ту ее часть, данные которой отвечают определенным условиям.
Фильтр - инструмент Excel, позволяющий отобразить на экране только записи, удовлетворяющие определенному условию.
Для успешной фильтрации данных в Excel, так же как и для сортировки, рекомендуется проверить, как организованы заголовки в таблице, и при необходимости вставить строку с условными заголовками столбцов.
В Excel существуют два вида фильтров: автофильтр и расширенный фильтр.
Фильтрация по простым критериям реализуется с помощью автофильтра.
После выделения всех данных таблицы, включая заголовки столбцов, следует выполнить команду Данные\Фильтр\Автофильтр. Около заголовка каждого из столбцов появится стрелка.
Если щелкнуть на стрелке, например, в столбце Продавец (См. рис. 18), то в появившемся окне можно выбрать любую из фамилий, например, фамилию Ивин.
Рис. 18. Таблица с Автофильтром
В результате этих действий в таблице останутся только строки, содержащие фамилию продавца Ивин.
Если затем щелкнуть на стрелке Район и выбрать район Южный, то количество записей уменьшится, т.к. теперь останутся только записи, удовлетворяющие обоим условиям: Продавец = Ивин Район = Южный
Рис. 19. Диалоговое окно Пользовательский автофильтр
В результате этих действий останутся только строки с данными, удовлетворяющими всем указанным условиям.
Условия, задаваемые на значения разных столбцов, Важно1 рассматриваются при фильтрации, как условия,
выполняемые одновременно (их связывают
отношения типа логического И).
Номера отфильтрованных строк приобретают голубой цвет. Строки, не удовлетворяющие критериям фильтрации, не удаляются, а временно скрываются.
Чтобы вернуть на экран все строки таблицы, нужно выполнить команду Данные\Фильтр\Отобразить все в списках условий около каждого заголовка.
Некоторые критерии фильтрации не могут быть описаны средствами автофильтров. В этом случае используют инструмент Расширенный фильтр.
Расширенный фильтр.
Для применения расширенного фильтра необходимо предварительно подготовить т.н. Диапазон условий и Диапазон, в который будут помещены результаты.
Для организации Диапазона условий следует:
¦ в свободную строку вне таблицы скопировать заголовки тех столбцов, на данные которых будут наложены ограничения (заголовки несмежных столбцов могут оказаться рядом);
¦ под каждым из заголовков задать условие отбора данных.
Условия, находящиеся в одной строке Excel Важно! рассматривает, как условия И, условия, находящиеся
в разных строках как условия типа ИЛИ.
Строка копий заголовков вместе с условиями отбора и образуют Диапазон условий.
ПРИМЕР 16. Пример отбора записей с помощью расширенного фильтра
Для табл. 14 приме
ром диапазона условий может служить диапазон:
|
||||||
| Табл. 17. Пример Диапазона условий |
Если же в результатах фильтрации будут фигурировать только данные из определенных столбцов, то заголовки этих столбцов предварительно (до фильтрации) следует скопировать в строку в свободном месте экрана и указать их в окошке Поместить результат в диапазон.
Примером такого диапазона может служить диапазон:
Объем (шт.)
Район
Продукция
Табл.18. Пример диапазона, в который помещается результат
После подготовки указанных диапазонов можно приступать к фильтрации данных. Для этого следует выполнить команду Данные\Фильтр\Расширенный фильтр. В появившемся диалоговом окне необходимо указать:
1. где следует размещать отфильтрованные данные (мы рекомендуем выбрать опцию скопировать результат в другое место);
2. Исходный диапазон - все данные вместе с заголовками столбцов;
3. подготовленные Диапазон условий (табл. 17) и Поместить
результат в диапазон (табл. 18).
При организации расширенного фильтра удобно использовать имена диапазонов и ячеек.
ПРИМЕР 17. Пример формирования условий для применения расширенного фильтра.
Рассмотрим следующую задачу:
В таблице 14 найти записи, соответствующие январю месяцу, имеющие данные по Выручке, большие 1000000 и записи, соответствующие февралю месяцу, имеющие данные по Выручке, большие 150000.
Создадим Диапазон условий. Он будет иметь следующий вид:
|
||||||
| Табл. 19. Пример диапазона условий для примера 17 |
Создание и редактирование сводных таблиц.
Сводная таблица - это таблица, которая используется для анализа данных в таблицах. В сводной таблице автоматически создаются общие и промежуточные итоги. Для таблицы, приведенной в табл. 14, одним из вариантов сводной таблицы является таблица, приведенная на рис. 20.
Рис. 20 Пример сводной таблицы
|
||||||||||||||
Подведение итогов в сводной таблице производится с помощью итоговых функций СУММА, СРЕДНЕЕ ЗНАЧЕНИЕ и др.
Создание сводной таблицы.
Перед созданием сводной таблицы рекомендуется проверить, как организованы заголовки столбцов с данными.
- если заголовок каждого столбца размещен строго в одной ячейке, расположенной непосредственно над столбцом, то такая таблица не требует предварительной подготовки;
- в противном случае, перед первой строкой данных в таблицу следует вставить строку с условными (вспомогательными) заголовками.
Чтобы создать сводную таблицу, нужно выполнить команду Данные\Сводная таблица. Этой командой запускается Мастер сводных таблиц, позволяющий ввести информацию для создания сводной таблицы.
В первом диалоговом окне следует указать, где находятся обрабатываемые данные. В нашем примере данные находятся на одном листе и поэтому надо выбрать опцию Данные находятся в списке или базе данных Microsoft Excel, а затем нажать кнопку Далее.
В следующем диалоговом окне следует указать диапазон, содержащий исходные данные. Для этого необходимо мышью выделить исходную таблицу, включая заголовки столбцов. Если организация таблицы потребовала вставки строки с условными заголовками столбцов, то в качестве исходного диапазона указываются исходные данные вместе с условными заголовками.
В следующем диалоговом окне необходимо указать, где будет размещена сводная таблица - на имеющемся листе или на новом. Мы рекомендуем располагать сводные таблицы на отдельных листах.
После этого нажмите кнопку Макет.
Следующее диалоговое окно позволяет сконструировать макет создаваемой сводной таблицы (см. рис. 21).
Рис. 21 Макет сводной таблицы
Для организации сводной таблицы необходимо:
- перетащить с помощью мыши кнопки полей, соответствующие описательным данным таблицы в т.н. область сведения, которая представлена двумя прямоугольниками белого цвета Столбец и Строка. Выбор перетаскиваемых кнопок и их расположение определяются требованиями решаемой задачи.
Пример создания сводной таблицы.
- перетащить в область Данные кнопки тех полей, по которым будут подводиться итоги.ПРИМЕР 18 Пример создания сводной таблицы.
Для получения сводной таблицы, представленной на рис. 20, необходимо перетащить кнопку Продавец в область Столбец, а кнопку Выручка в руб. в область Данные.
Макет сводной таблицы представлен на рис. 22.
Рис. 22. Вид окна Мастера сводных таблиц
Редактирование сводной таблицы
Одним из вариантов редактирования сводной таблицы является возврат к ее макету. Для того чтобы активизировать макет сводной таблицы,
- необходимо щелкнуть в любой ячейке сводной таблицы;
- вызвать команду Данные\Сводная таблица или нажать кнопку Мастер сводных таблиц на панели инструментов Сводные таблицы.
В появившемся диалоговом окне можно изменить структуру сводной таблицы, перетащив кнопки полей.
Для изменения функции, с помощью которой подводятся итоги, достаточно два раза щелкнуть мышью на кнопке, размещенной в поле данных макета сводных таблиц. В появившемся диалоговом окне (см. рис.
23) можно выбрать новую функцию.
Удобным вариантом организации сводных таблиц является размещение одной или нескольких кнопок полей в Поле страниц (на макете сводных таблиц это окно представлено белым прямоугольником с надписью Страница.,)
Если в приведенном выше примере поместить кнопку Продавец на поле страниц, то сводная таблица примет следующий вид:
Рис. 23. Диалоговое окно Вычисление поля сводной таблицы
|
||||||||||||||||
При необходимости размещения данных по каждому продавцу на отдельных листах рабочей книги нужно:
- щелкнуть мышью в любом поле сводной таблицы;
- на панели инструментов Сводная таблица нажать одноименную кнопку. В появившемся меню выбрать команду Отобразить страницы.
В результате Excel вставит столько листов в рабочую книгу, сколько фамилий продавцов фигурировало в исходной таблице.
Если данные в исходной таблице периодически редактируются, то для обновления сводной таблицы необходимо:
- щелкнуть мышью в любом поле сводной таблицы;
- на панели инструментов Сводная таблица нажать кнопку Обновить данные!
Если в таблицу, по которой строится сводная, периодически добавляются новые строки, то рекомендуется:
- при построении сводной таблицы использовать
СОВЕТ: имя, заранее присвоенное диапазону,
включающему заголовки столбцов, и
анализируемые данные;
- новые строки вставлять внутрь исходного диапазона.
Если необходима информация, иллюстрирующая конкретное итоговое значение, то следует два раза щелкнуть на нем мышью. При этом в рабочую книгу будет вставлен новый лист с теми строками исходной таблицы, которые содержат данные, использовавшиеся для получения выбранного итогового значения.
Очень наглядными являются диаграммы, построенные на сводных таблицах.
Диаграммы для сводных таблиц.
Перед построением диаграммы удалите любые промежуточные итоги из сводной таблицы. Для этого в Мастере сводных таблиц установите указатель на кнопку поля, из которого следует удалить промежуточные итоги, и дважды щелкните на нем кнопкой мыши. В появившемся диалоговом окне (см. рис. 25) установите переключатель Итоги в положение нет. Нажмите ОК.
Для построения диаграммы щелкните в любом месте сводной таблицы и нажмите на кнопку Мастер диаграмм.
Отредактируйте появившуюся диаграмму.
Рис. 25. Диалоговое окно Вычисление поля сводной таблицы, позволяющее
Excel предлагает несколько инструментов, относящихся к категории Что-Если. К ним относятся Подбор параметров, Сценарии, Таблицы подстановки, Поиск решения.
Подбор параметров.
Инструмент Подбор параметров позволяет находить необходимый результат, изменяя при этом одну переменную. Перед применением рассматриваемого инструмента следует решить задачу средствами Excel с любыми входными параметрами.
ПРИМЕР 19. Пример использования инструмента Подбор параметра
Рассмотрим следующую задачу: Какую максимальную ссуду на год можно взять, если вы должны ограничить ежемесячные выплаты суммой 250 р. процентная ставка - простая, 8% годовых.
1. На первом шаге следует найти величину ежемесячных выплат для произвольной величины ссуды по формуле:
Платеж=(1+Процентная ставка)Величина займа/Срок в
месяцах. Для этого в ячейке В5 следует ввести формулу: =(1+В2)В1/В3. (Вариант оформления листа Excel для решения задачи приведен на рис.26).
Рис. 26.
Фрагмента рабочего листа Excel.
|
|||||||||||||||||||||||||
- Выберите команду Сервис \Подбор параметра.
- В появившемся диалоговом окне (см. рис. 27) в окне Установить в ячейке введите ссылку на ячейку, содержащую формулу (в нашем примере B5.)
- Введите искомый результат в поле Значение (в нашем примере 250р.)
Рис. 27. Диалоговое окно Подбор параметра
Изменяемые ячейки в модели - это ячейки, содержащие значения, которые требуется использовать в качестве переменных.
Для применения Сценариев следует на рабочем листе Excel смоделировать решение задачи при некотором наборе входных параметров.
ПРИМЕР 20. Пример использования сценариев при анализе поведения модели.
На рис. 28 приведен вариант расчета расходов фирмы. Требуетсясоставить варианты расходов на следующий год.
Заметим, что ячейка Расходы содержит формулу - сумму величин:
Арендная плата, Коммунальные услуги, Административные расходы.
1. Выберите команду Сервис\Сценарии. В появившемся диалоговом окне нажмите кнопку Добавить.
2. В окне Добавление сценария (См. рис. 29) введите имя первого сценария.
В нашем примере в качестве имени сценария можно выбрать имя Прошлый год А затем в этом же диалоговом окне следует указать адреса изменяемых ячеек ( в нашем примере - это адреса $В$1: $B$3).
Важно!
При указании несмежных диапазонов удерживайте нажатой клавишу Ctrl.
| А | В | |
| 1 | Арендная плата | 120 000,00 р. |
| 2 | Коммунальные услуги |
100 000,00 р. |
| 3 | Админ.расходы | 25 000,00 р. |
| 4 | Расходы | 245 000,00 р. |
| ? |
3. Нажмите кнопку ОК.
Рис. 29. Диалоговое окно Изменение сценария
После ввода значений параметров нажмите ОК. Рекомендуем реальные данные, например, по прошлому году сохранить как один из сценариев.
5. Чтобы создать другой сценарий снова нажмите кнопку Добавить, задайте имя нового сценария, например, Оптимистический прогноз, нажмите ОК, задайте оптимистичные прогнозные значения для арендной платы, коммунальных услуг и административных расходов и еще раз нажмите ОК.
6. После формирования сценариев можно перейти к анализу модели. Существует возможность их поочередного просмотра.
Для этого в окне Диспетчер сценариев следует выбрать имя сценария и нажать кнопку Вывести.
1. При работе со сценариями удобно ячейкам с изменяемыми значениями и ячейкам с результатами присваивать имена.
Замечания.
2. Рекомендуется исходный вариант модели сохранить как один из сценариев.
3. При сохранении рабочей книги Excel сценарии сохраняются вместе с другими данными рабочего листа.
Создание отчетов по сценариям.
При нажатии кнопки Отчет в окне Диспетчера сценариев, появляется диалоговое окно, позволяющее выбрать тип отчета. Для простых моделей задач с небольшим количеством изменяемых ячеек рекомендуется тип отчета Структура.
В этом же окне следует указать ячейки, с формулами, зависящими от изменяемых в сценариях параметров. После нажатия кнопки ОК будет вставлен новый лист с данными о поведении модели при различных сценариях поведения
| Структура сценария | Текущие значения: |
Прошлый год |
Оптими-стический прогноз |
Пессими стический прогноз |
| Изменяемые: | ||||
| Арендная_плата | 12000,00 | 12000,00 | 1200,00 | 120000,00 |
| Коммунальные_услуги | 1000,00 | 1000,00 | 100,00 | 10000,00 |
| Админ.расходы | 2500,00 | 2500,00 | 2500,00 | 2500,00 |
| Результат: | ||||
| Расходы | 15500,00 | 15500,00 | 3800,00 | 132500,00 |
При вводе повторяющихся данных в таблицу очень удобно использовать так называемые поля со списками, организовать которые позволяет инструмент Проверка данных.
Рассмотрим пример организации такого поля со списком.
Предположим, что возникает необходимость регулярно вводить в столбец одни и те же данные, например, названия преподаваемых в школе предметов.
На первом шаге следует на отдельном листе сформировать список предметы и присвоить ему имя - ПРЕДМЕТЫ.
На втором шаге нужно переключиться на лист с заполняемой таблицей.
Далее необходимо выделить диапазон, в котором будут использоваться данные из списка ПРЕДМЕТЫ и выполнить команду Данные\Проверка. В появившемся диалоговом окне (См. рис.32) на закладке Параметры ввода следует щелкнуть на стрелке в первом окне Тип данных и выбрать пункт Список. Далее нужно щелкнуть в окне Источник данных и нажать клавишу F3.
В появившемся диалоговом окне щелкните на имени ПРЕДМЕТЫ и нажать кнопку ОК.
Рис.32. Диалоговое окно Проверка вводимых значений
Рис33. Поле со списком для ввода
|
||||||||||||||||||||
значений, сообщения об ошибке и т.д.
Пример 21 создания теста с использованием ввода данных из фиксированного списка
Используя оптимальные технологии ввода данных, создайте средствами MS EXCEL тест.
В ячейку С3 введите вопрос 1 - В каком году был разработан план застройки г.Санкт-Петербург?
Поскольку текст вопроса в ячейку не поместился, измените ширину столбца.
В ячейки, F3, G3, H3, I3 соответственно введите ответы: 1716 1803 1723 1730 (рис.34).
Рис.34. Первый вопрос с ответами
Рис.35 . Проверка вводимых значений
В окне ввода Источник нажмите на кнопку 5;, после чего выделите ячейки с ответами (в нашем случае это ячейки F3, G3, ИЗ, 13).
Теперь нажмите на кнопку J^l и далее, на кнопку ОК.
В результате вышеизложенных действий, около ячейки C4 появится кнопка для выбора. Нажав ее, получим список ответов.
Выбранный ответ помещается в ячейку С4 (См, рис.36),
Рис.36. Список ответов
Мы знаем, что правильный ответ хранится в ячейке F3 (для данного вопроса). Сравним содержимое в ячейке C4 с содержимым в ячейке F3.
Если они совпадают, то выбран правильный ответ и в ячейку поместим слово верно, если не совпадают, то выбран не правильный ответ и в ячейку Е4 поместим слово неверно.
Выделим ячейку Е4 и вставим туда функцию проверки. Для этого вызовем Мастер функций.
В списке Категория выберем Логические В списке функций выберем ЕСЛИ. Нажмем на кнопку OK.
В функции ЕСЛИ укажем:
- в окошке Логическое_выражение: C4=F3
- в окошке Значение_если_истина: "верно "
- в окошке Значение_если_ложь: "неверно"
Нажмем кнопку ОК (См. рис.37).
Рис.37. Аргументы функции ЕСЛИ
=ЕСЛИ(С4=Б'3; верно неверно )
А в ячейке Е4 реакцию на ответ.
| С | D | Е |
| История Санкт -Петербурга | ||
| Б каком году Оыл раграОотап илап застройки. г. Санкт-Петербург? | ||
| SS03 | ? неверно |
Аналогично оформляем следующие вопросы.
Подсчет правильных ответов, организуем, используя функцию
СЧЁТЕСЛИ
Создание пользовательского списка.
К оптимальным технологиям ввода относится также ввод данных с помощью т.н. пользовательского списка.
Известно, что если в одну из ячеек Excel таблицы ввести название Январь и скопировать его с помощью черного креста, то в остальных ячейках появятся названия остальных месяцев: Февраль, Март и т.д. Если ввести в ячейку Понедельник и скопировать его черным крестом, то появятся остальные дни недели.
Можно также использовать сокращенные названия Янв и Пн.
По аналогии с указанными возможностями, пользователь может создать свои списки учеников, учителей, предметов и т.д., выводимые с помощью копирования первого элементы списка.
Для создания пользовательского списка следует выполнить команду Сервис\Параметры и на закладке Списки (См. рис.34) в окошке Элементы списка ввести элементы вашего списка, разделяя их нажатием клавиши Enter. Затем следует нажать кнопку Добавить, а затем кнопку ОК.
Если список уже сформирован, то его можно импортировать. Для этого следует в том же диалоговом окне щелкнуть в окошке Импорт списка из ячеек, указать диапазон, содержащий уже введенный список и нажать кнопку Импорт.
Пользовательский список должен появится в окне Списки.
Теперь для вывода полного списка элементов, на любом листе любого файла можно ввести первый элемент списка, а затем скопировать его черным крестом.
Рис.39. Вкладка Списки
Пример 22. Пример создания пользовательского списка.
Создадим пользовательский список, состоящий из наименований предметов: История средних веков, Информатика, Русская литература, Математика.
Выполните команду Сервис\Параметры и на закладке Списки (См. рис.39) в окошке Элементы списка
- введите слово Предметы, нажмите клавишу Enter;
- введите: История средних веков, нажмите клавишу Enter;
- введите Информатика, нажмите клавишу Enter.
- введите Русская литература., нажмите клавишу Enter и т.д. Указанные предметы должны располагаться в столбце окошка
Элементы списка;
- нажмите кнопку Добавить, список предметов появится в окне Списки;
- нажмите кнопку ОК.
Теперь рассмотрим, как можно использовать сформированный пользовательский список. Введите в любую ячейку слово Предметы и
скопируйте его с помощью черного креста на четыре ячейки вниз. Все элементы списка появятся на листе.
Заметим, что слово Предметы мы ввели в качестве первого элемента списка исключительно для удобства, чтобы не набирать сложный текст История средних веков.
Работа со справочниками. Использование функции ВПР.
Рассмотрим одну из функций MS Excel - ВПР, облегчающую работу по заполнению таблиц при необходимости использования справочной информации. Примером может служить заполнение отчетов, в которых при вводе номера личного дела ученика в соседних столбцах должны автоматически появляться его имя, фамилия, телефон, адрес, данные о родителях и т.д.
На первом шаге, на отдельном листе создайте справочную таблицу (См. табл.20) и присвойте ей имя Справочник, например:
|
|||||||||||||||
| Табл. 20. Справочник товаров |
|
|||||||||||||||
| Табл. 21. Фрагмент отчета |
Функция ВПР имеет следующие аргументы:
Искомое_значение: в этом окошке указывается значение ( личного дела), определяющее остальные параметры (ФИО ученика, его телефон). Это значение будет искаться в первом столбце таблицы-справочника.
Таблица: в этом окошке указывается адрес или имя справочной таблицы (в нашем примере - это имя справочник).
Номер_столбца: в этом окошке указывается номер столбца, содержащий соответствующую характеристику значения, введенного в первом окошке (в нашем случае номер 2 определяет столбец ФИО).
Интервальный_просмотр: имеет два значения 0, если таблица-справочник не отсортирована по первому столбцу, и 1, если она отсортирована по первому столбцу.
Для определения телефона ученика, личного дела которого будет введен в ячейке А2, следует ввести формулу: ВПР(А2;справочник;3;0).
Рис.40. Диалоговое окно функции ВПР
Реализовать это можно с помощью функции ЕСЛИ. В рассматриваемой ситуации функция будет иметь вид:
=ЕСЛИ(А2=0;; ВПР(А2;справочник;2;0)).
При вводе номера личного дела ученика в столбцах ФИО и Телефон появятся соответствующие значения.
Для ввода номеров личных дел мы рекомендуем использовать возможность MS Excel по вводу значений из фиксированного списка с помощью инструмента Данные\Проверка.
Отличия при использовании функций в Excel 2000
Использование функций в Excel 2000 производится по следующей схеме:
1. Установить курсор в той ячейке, где ожидается получить ответ.
2.
3.
Вызвать Мастер функций нажатием кнопки _ на панели
Стандартная.
В левой части первого диалогового окна (См. рис. 41) следует выбрать категорию функции (математическая, статистическая), а затем в правой части этого же диалогового окна щелкнуть на названии необходимой функции и нажать кнопку ОК.
Рис. 41. Окно Мастера функций
5. Нажать кнопку ОК.
Библиографический список:
1. П. Блатнер, Л. Ульрих. Использование Microsoft Excel 2000.Москва. Вильямс.
2000г.1020с.
2. М. Додж, К.Кината, К Стинсон. Эффективная работа c Microsoft Excel.
Санкт-Петербург. 1997.1040с.
3. Разработка бинес-приложений в экономике на базе MS EXCEL. Под общ. ред.
А.И. Афоничкина. - М. ДИАЛОГ - МИФИ.
2003. -416с.
Базы данных: Разработка - Управление - Excel
- Базы данных
- Разработка баз данных
- СУБД и базы данных
- Управление базами данных
- Классика баз данных
- Софт для создания базы данных
- SQL
- Access
- FoxProо
- Расширенная оптимизация подзапросов в Oracle
- Informix
- Линтер
- Postgres
- СУБД DB2
- InterBase
- Excel
- Таблицы Excel
- Справка Excel
- Программирование в Excel
- Деньги в Excel
- Задачи Excel