Excel представляет много возможностей для анализа данных. Простейшие из них это сортировка, условное форматирование, логические функции. Используем эти возможности в процессе решения методической задачи - поэлементного анализа выполнения контрольной работы. Одновременно познакомимся с такими средствами обработки данных, как макросы и элементы управления.

Каждая контрольная работа проверяет не знания вообще, а определенные элементы знаний. В большой (45 мин.) контрольной работе может проверяться 6-8 элементов знаний, относящихся как к последней изученной теме, так и к темам изученным ранее. Не обязательно у каждого ученика должны быть проверены все элементы знаний - это может привести к слишком объемным заданиям. С учетом примерно равной сложности элементы распределяются между несколькими вариантами, ученик, выполняя один из вариантов, показывает усвоение некоторых элементов знаний, но работы всего класса в целом дают полную картину усвоения темы.

Для того, чтобы контрольная работа в полной мере выполняла свои диагностические функции, необходимо проводить ее поэлементный анализ. Он позволяет выявить не только оценку отдельного ученика, но и усвоение каждого поставленного на контроль элемента знаний. Для проведения такого анализа в ходе проверки работы заполняется таблица, приведенная в файле Контроль0.xls для некоторой контрольной работы (исходный файл). В этой контрольной работе шла проверка по двум темам, 6 элементам. В таблице удачные ответы отмечены буквой "х" (хорошо), неудачные "п" (плохо).

I Запустите Excel, откройте файл Контроль0.xls, сохраните в папке НИТ на рабочем столе под именем Фамилия-Контроль.xls (тем самым создаем копию). Рассмотрите таблицу данных на листе КР. Заполните список фамилий (произвольно).

Статистическая функция СЧЁТЕСЛИ подсчитывает число ячеек внутри интервала, удовлетворяющих заданному критерию.
Синтаксис: СЧЁТЕСЛИ(интервал; критерий), где
Интервал - это интервал, в котором нужно подсчитать ячейки.
Критерий - это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки". Критерий может быть задан и адресом ячейки, содержащей нужные данные.

Для ввода функции выделяют нужную ячейку и вызывают Мастер функций с помощью кнопки вставки функции fx (или через меню Вставка-Функция). В окне мастера функций на первом этапе выбирают нужную категорию функций (Статистические) и в списке функций (упорядоченном по алфавиту) находят функцию СЧЁТЕСЛИ.
На втором этапе определяют аргументы функции. Интервал ячеек вводят указывая мышкой, критерий - вводя с клавиатуры (текст, буквы указываются в кавычках). После ввода аргументов нажимают кнопку Ok.

I Используя функцию СЧЕТЕСЛИ, подсчитайте число хороших и плохих ответов по каждому элементу знаний и для каждого ученика (используйте прием протягивания). В ячейках D20:I20 определите соответственно усвоение элементов знаний в процентах. по формуле %усв = N(хороших)/N(общее).
В ячейках L4:L17 определите балл ученика по формуле:
балл = 5*N(хороших)/N(общее)

Функция СУММЕСЛИ суммирует содержимое ячеек, определяемых заданным критерием.
Синтаксис: СУММЕСЛИ(Диапазон; Критерий; Диапазон_сумм), где
Диапазон - это интервал ячеек, в которых проверяется заданный критерий. В ячейках могут находиться числа или текст.
Критерий - это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Выражения (напр. ">=1") и текст вводятся в кавычках. Может быть также указан адрес ячейки, где находится критерий.
Диапазон_сумм - это диапазон ячеек, содержимое которых суммируется, если соответствующие им ячейки в аргументе Диапазон удовлетворяют заданному критерию. Если Диапазон_сумм не указан, то суммируются ячейки в аргументе Диапазон.

I Используя функцию СУММЕСЛИ, подсчитайте число хороших и плохих ответов по теме 1 и по теме 2. Диапазон проверки строка 3 (номера тем), диапазон суммирования строки 18 и 19 (соответственно "хорошо" и "плохо"). Задавая критерий, щелкните по заголовку одной из ячеек, содержащих нужный текст ("тема 1" или "тема 2") Определите усвоение каждой темы в процентах. (Диапазон E23:F25).

Функция ЕСЛИ возвращает одно значение, если заданное условие истинно, и другое - если оно ложно. Используется для условной проверки значений и формул.
Синтаксис: ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь), где
Лог_выражение - это любое значение или логическое выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Значение_если_истина - это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА. Может быть указан текст, число, адрес ячейки, или другая формула. Если этот аргумент отсутствует, и лог_выражение имеет значение ИСТИНА, то возвращается слово "ИСТИНА".
Значение_если_ложь - это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ. Если этот аргумент отсутствует, и лог_выражение имеет значение ЛОЖЬ, то возвращается слово "ЛОЖЬ". Значение_если_ложь может быть другой формулой.

Вложенные функции. В некоторых случаях может потребоваться в качестве аргументов функции использование другой функции. Например, в логическое выражение функции ЕСЛИ могут входить другие логические функции (И, ИЛИ и т.п.) или математические (функция СРЗНАЧ). В формулах можно использовать до семи уровней вложения функций.
Так, до 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь, чтобы конструировать более сложные проверки. Рассмотрим пример:
Предположим, что нужно назначить буквенную категорию числам, на которые ссылаются по имени Балл. Категории приведены в следующей таблице:

Балл	Больше 89	От 80 до 89	От 70 до 79	От 60 до 69	Меньше 60
Категория	A	B	C	D	F

Тогда можно использовать три вложенные функции ЕСЛИ:
ЕСЛИ(Балл>89;"A";ЕСЛИ(Балл>79;"B";ЕСЛИ(Балл>69;"C";ЕСЛИ(Балл>59;"D";"F"))))
В этом примере второе предложение ЕСЛИ является в то же время аргументом значение_если_ложь для первого предложения ЕСЛИ. Аналогично, третье предложение ЕСЛИ является аргументом значение_если_ложь для второго предложения ЕСЛИ. Например, если первое лог_выражение (Среднее>89) имеет значение ИСТИНА, то возвращается значение "A". Если оно имеет значение ЛОЖЬ, то вычисляется второе предложение ЕСЛИ и так далее.

Для ввода функции ЕСЛИ как и ранее используем Мастер функций. На первом шаге выбираем категорию функций Логические, затем находим функцию.
Чтобы ввести другую функцию в качестве аргумента, можно использовать список функций рядом со строкой формул. Например, чтобы добавить в текстовое поле значение_если_ложь новую функцию ЕСЛИ надо щелкнуть по слову ЕСЛИ в этом списке. При этом автоматически откроется новое окно аргументов для новой функции ЕСЛИ, в котором можно будет задавать аргументы уже для этой функции, в том числе и вкладывать в нее другие функции.
Чтобы переключиться между несколькими функциями, входящими в состав формулы, щелкните мышкой на имени нужной функции в строке формул,. При этом откроется окно аргументов нужной функции.

I Используя вложенную функцию ЕСЛИ, выразите словесную оценку работы первого ученика исходя из его балла в ячейке М4, используя словесные характеристики в ячейках N3:N7 и их критерии в ячейках О3:О6. Логическое выражение вводится с использованием мышки и клавиатуры, например L4>=$O$4 - обратите внимание на сочетание относительных адресов и абсолютных - это позволит применить прием "протягивания". Используя протягивание, введите эту формулу для всех учеников в списке (M4:M17). Изменяя критерии, проверьте правильность работы формул.

Анализу данных помогает выделение определенных ячеек в зависимости от их значений или результатов вычислений. Для этого используется условное форматирование. Так для наглядности можно выделить ячейки таблицы, содержащие букву "п".

Задание условного формата в ячейках
1. Выделите ячейки, формат которых необходимо установить.
2. Выберите команду меню Формат - Условное форматирование.
3. Задайте условие. Чтобы в качестве условия форматирования использовать значения выделенных ячеек, выберите параметр Значение, выберите операцию сравнения, а затем введите необходимое значение. Ввести можно заданное значение или формулу, но перед формулой необходимо поставить знак равенства (=).
Чтобы в качестве условия форматирования использовать данные других ячеек выберите параметр Формула, а затем в поле справа введите формулу. Формула должна принимать логическое значение ИСТИНА или ЛОЖЬ.
4. Нажмите кнопку Формат и задайте нужный формат ячейки (шрифт, его цвет, подчеркивание, рамку и т.д.). Выбранные форматы будут применены, только если значение ячейки отвечает поставленному условию или если формула принимает значение ИСТИНА.
5. Указать можно до трех условий. Для добавления условия нажмите кнопку А также >>, а затем повторите шаги 3-5.

Если из нескольких указанных условий и более одного принимают истинное значение, то применяется только тот формат, который соответствует первому истинному условию. Если ни одно из заданных условий не принимают истинного значения, то формат ячеек остается без изменения. Существующий формат можно использовать для определения четвертого условия.

Условные форматы можно копировать в другие ячейки (кнопка Формат по образцу). Для удаления одного или нескольких условий выберите команду меню Формат-Условное форматирование, нажмите кнопку Удалить, а затем установите флажки для тех условий, которые необходимо удалить.

=> Особенности Office-2007. - В Excel-2007 команды условного форматирования находятся на вкладке Главная. данной работе надо использовать Правила выделения ячеек. Нужное правило выбирается в открывающемся рядом списке правил ("больше", "меньше", "текст содержит" и др.) В открывшемся окне вводится необходимый текст, число или адрес ячейки, содержащей нужный критерий. Затем можно выбрать один из предложенных форматов или выбрать Пользовательский формат для задания собственных параметров формата. Чтобы создать правило выделения одних ячеек в зависимости от значений других, необходимо выбрать Другие правила и использовать формулу. При создании формулы для диапазона ячеек необходимо, чтобы адреса ячеек сравнения были относительными. В Excel-2007 существует много быстрых способов форматирования с использованием цветовых шкал, гистограмм, значков и др. Число правил условного форматирования может быть больше трех, их можно перемещать в списке, изменяя их Приоритет.

I Используя условное форматирование, выделите все ячейки, отвечающие плохим ответам ("п"). Задайте условный формат для фамилий учеников в зависимости от их оценок. (фамилии отличников красным и т.п.). В Excel-2007 - задайте условный формат для ячеек "балл" (L4:L17), используя гистограммы или значки. Убедитесь, что условное форматирование работает, произвольно изменяя содержимое ячеек таблицы. Для возврата к исходному состоянию используйте кнопку "Отменить".

Одним из способов анализа данных является их представление в графическом виде. Для сравнения однородных величин обычно используется столбиковая диаграмма (гистограмма), для рассмотрения соотношения частей и состава - круговая диаграмма.
Построение диаграммы аналогично построению графика:
1) Выделите массивы ячеек, содержащих необходимые данные. (Несмежные области выделяют, используя клавишу Ctrl).
2) Щелкните на значке Мастера диаграмм панели инструментов. На первом этапе его работы определите тип диаграммы и ее вид.
3) На следующем этапе можно при необходимости скорректировать диапазон и ряды данных.
4) Следующий этап позволяет ввести различные надписи, определить необходимость и расположение легенды, линий сетки
5) На последнем этапе определяем расположение диаграммы и нажимаем кнопку Готово.

=> Особенности Office-2007. - В Excel-2007 нет мастера диаграмм. Первый этап выполняется с вкладки Вставка. При этом на листе сразу появляется выбранный тип диаграммы. При щелчке на диаграмме на ленте появляются три дополнительные вкладки, предназначенные для работы с диаграммами. На вкладке Конструктор можно изменить тип диаграммы, определить исходные данные, если они не были выделены предварительно, выбрать макет, стиль и размещение диаграммы. Вкладка Макет позволяет проводить более детальную работу с элементами диаграммы (Легенда, Название, Подписи, Сетка, Оси и т.д.) Вкладка Формат предоставляет дополнительные возможности форматирования объектов диаграммы.

IПостройте гистограмму усвоения отдельных элементов знаний (%%) по данным ячеек D20:I20. С помощью функции СЧЕТЕСЛИ посчитайте число учащихся имеющих оценки "отлично", "хорошо" и т.д. (ячейки N21:N24). По этим данным постройте круговую диаграмму успеваемости класса.

Для сортировки данных выделяют массив ячеек и используют команду меню Данные - Сортировка. Можно задать до трех условий сортировки, определить характер (по возрастанию, по убыванию). Можно задать пользовательский порядок сортировки, например, по названиям химических элементов водород, гелий, литий, и т.д. Сортировку можно проводить не только в столбцах, но и в строках, изменив соответствующий параметр. Следует учитывать, что неосторожная сортировка может привести к перемешиванию данных.

=> Особенности Office-2007. В Excel-2007 команды сортировки есть на вкладках Главная и Данные. При использовании быстрых команд сортировка идет по первому столбцу диапазона. Чтобы задать для критерия сортировки другой столбец, установить несколько критериев используется команда Настраиваемая сортировка. Число условий сортировки может быть больше трех. Если диапазон ячеек или столбец таблицы был отформатирован вручную или с помощью условного форматирования с применением цвета ячеек или цвета шрифта, можно производить сортировку по цветам. Можно также производить сортировку по набору значков, созданных с помощью условного форматирования.

I Для продолжения работы скопируйте (используя ссылки) на лист Рейтинг данные из столбцов №, фамилия, балл, оценка. Отсортируйте данные в порядке убывания балла ученика, затем в алфавитном порядке фамилий. Вернитесь к исходному состоянию отсортировав по № списка.

Макрос - это компьютерная программа, которая представляет собой последовательность команд, которые в дальнейшем будут выполняться как одна команда. Макросы позволяют автоматизировать выполнение сложных, многоходовых задач. Макросы можно непосредственно создавать в редакторе Visual Basic, который является модулем всех программ Microsoft Office. Наиболее простой способ создания макроса, не требующий знания языка программирования - это запись некоторой последовательности действий, выполняемой человеком.
Как любые программы, макросы могут создаваться и как вирусы. Макровирусы - это один из видов компьютерных вирусов. Они распространяются через книги Excel и другие файлы Microsoft Office. Когда открывается зараженная книга или начинается действие, активизирующее макровирус, он может переместиться на незащищенный компьютер, где будет располагаться в скрытой книге. Начиная с этого момента, любая книга может быть "инфицирована". Если кто-то другой воспользуется зараженной книгой, макровирус может быть перенесен на другие компьютеры.
Чтобы предотвратить распространение вирусов, в программах Microsoft Office используются разные уровни безопасности при работе с макросами. При среднем уровне безопасности Microsoft Excel выводит предупреждающее сообщение при попытке воспользоваться подозрительной книгой с макросами, и предлагает сделать выбор:
- Если книга должна содержать нужный макрос, выберите Включить макросы, чтобы разрешить запуск макросов.
- Если важного макроса в книге нет или о ее содержимом ничего не известно, выберите Отключить макросы.
При высоком уровне безопасности Microsoft Excel просто сообщит, что в книге есть макросы, но открыть их не позволит. Не позволит он и создавать новые макросы. В этом случае для включения макросов придется изменить уровень безопасности. Для этого:
- откройте файл с отключенными макросами,
- в меню Сервис-Макрос-Безопасность установите средний уровень безопасности;
- закройте файл без сохранения и снова его откройте.

=> Особенности Office-2007. В Excel-2007 настройка безопасности осуществляется в разделе Центр управления безопасности (кнопка Office -Параметры Excel - Центр управления безопасности). В блоке Параметры макросов рекомендуется установить режим "Отключить все макросы с уведомлением". Для обеспечения безопасности в Excel-2007 файлы, содержащие макросы, сохраняются в специальном формате: .xlsm. Если файл, содержащий макросы, сохранять в обычном формате .xlsx, то макросы в нем сохранены не будут. Значки файлов, содержащих макросы, специально помечены. В режиме "Отключить все макросы с уведомлением" при открытии файла с макросами появляется сообщение: Кнопка Параметры позволяет включить макросы документа, если они необходимы.

I Определите уровень безопасности установленный для EXCEL на данном компьютере. Установите Средний уровень безопасности, необходимый для последующей работы.

Перед записью макроса перейдите на нужный лист книги Excel, продумайте всю последовательность действий, которую вы хотите записать в макрос.
1. В меню Сервис - Макрос и выберите команду Начать запись. Откроется диалоговое окно.
2. Введите имя для макроса в соответствующее поле. Первым символом имени макроса должна быть буква. Остальные символы могут быть буквами, цифрами или знаками подчеркивания. В имени макроса не допускаются пробелы.
3. При желании задайте сочетание клавиш для выполнения макроса в поле Сочетание клавиш. (CTRL+ буква, CTRL+SHIFT+ буква, где буква - любая буквенная клавиша)
4. В поле Сохранить в выберите книгу, в которой должен быть сохранен макрос. Чтобы создать краткое описание макроса, введите необходимый текст в поле Описание.
5. Нажмите кнопку OK. При этом появляется панель инструментов Остановка записи с двумя кнопками. Кнопка Остановка записи прекращает запись макроса.
Кнопка Относительная ссылка позволяет по ходу записи переключаться с относительных ссылок на абсолютные и обратно. По умолчанию, при записи макроса используются абсолютные ссылки. Макрос, записанный с абсолютными ссылками, при выполнении всегда обрабатывает те же ячейки, которые обрабатывались при его записи. Макрос, записанный с относительными ссылками, начинает свою работу с той ячейки, которая является активной в момент его запуска, и позволяет обрабатывать произвольные ячейки.

Периодический закон и Периодическая система хим. эл. Д.И. Менделеева (ФГОС)

6. Выполните действия, которые нужно записать.
7. Нажмите кнопку Остановить запись.

Выполнение макроса
1. В меню Сервис-Макрос и выберите команду Макросы.
2. В поле Имя макроса выберите имя нужного макроса.
3. Нажмите кнопку Выполнить. Если по ходу работы макроса нужно прервать его выполнение, нажмите кнопку ESC.

Просмотр и изменение текста макроса Текст макроса можно просматривать и редактировать в модуле Visual Basic, который обычно устанавливается вместе со всеми программами Microsoft Office. Для открытия текста макроса:
1. В меню Сервис-Макрос выберите команду Макросы.
2. В поле Имя макроса выберите имя нужного макроса.
3. Нажмите кнопку Изменить. Откроется окно редактора Visual Basic. Для изменения записанного макроса необходимо знакомство с редактором Visual Basic, и языком программирования, который используется для написания и изменения макросов Microsoft Excel. Используя панель инструментов редактора Visual Basic, можно запустить макрос, отследить его работу по шагам и т.п.
4. Закончив работу с макросом, закройте редактор Visual Basic

=> Особенности Office-2007. В Excel-2007 команды для записи и воспроизведения макроса размещены вкладке Вид. Для работы с макросами и элементами управления предназначена также специальная вкладка ленты Разработчик, которая по умолчанию выключена. Для ее включения надо поставить в Параметрах Excel (кнопка Office-Параметры Excel-Основные) галочку "Показывать вкладку Разработчик на ленте".

I На листе Рейтинг создайте макросы "Рейтинг" и "Алфавит" для сортировки данных. Познакомьтесь с их текстом и опробуйте в работе. Сохраните Файл в формате с поддержкой макросов (Office-2007).

Microsoft Excel дает возможность добавления в книгу кнопок, переключателей, флажков и других элементов управления. Элементы управления позволяют предложить пользователю выбор параметров или дать возможность запуска макросов для автоматизации задач. Кнопке можно поставить в соответствие существующий макрос или с помощью флажков или переключателей вывести данные в виде списка или изменить данные на листе.

Для создания элементов управления используется панели инструментов Формы и Элементы управления.
1. Откройте лист, на который нужно добавить элементы управления.
2. Используя команду меню Вид-Панели инструментов, выведите на экран панель инструментов Формы или Элементы управления.
3. На этой панели инструментов выберите нужный элемент управления.
4. На листе щелкните мышкой в нужном месте и с помощью мыши придайте элементу управления нужный размер.
5. При добавлении кнопки после ее нажатия выберите нужный макрос в поле Имя макроса. При добавлении другого элемента управления щелкните его правой кнопкой мыши и выберите команду Назначить макрос в контекстном меню. Если макрос не существует, можно записать или написать новый.

=> Особенности Office-2007. Используются команды на вкладке Разработчик или раздела Макросы вкладки Вид.

I Поместите, используя панель инструментов Формы, на лист Рейтинг две кнопки, дайте им соответствующие названия и свяжите с макросами для сортировки. Опробуйте в работе. Сохраните файл.

---

Задать вопрос
Наверх

---

< И К Т   |  Карта сайта   |  Главная страница >