Файл: эксель ч1.doc

Значения критериев могут быть получены, например, при помощи экспертных оценок. В столбе Итого указана сумма значений критериев. Например, в ячейке F8 записана формула:=B8+C8+D8+E8. В столбе Итого с учетом важности критериев (веса) указана сумма произведений Веса критерия на его значение. Например, в ячейке G8 записана формула: =B8+C8+D8+E8. В столбе Итого с учетом важности критериев (веса) указана сумма произведений Веса критерия на его значение. Например, в ячейкеG8 записана формула:=B$18*B8+C$18*C8+D$18*D8+E$18*E8. Значение коэффициентов важности (веса) должны выбираться из диапазона [0...1] и в сумме составлять 1.

Критерием выбора автомобиля может быть любая характеристика: стоимость, экономичность, надежность, престижность, итоговая сумма значений критериев или итоговая сумма значений с учетом веса. При использовании в качестве критерия итоговой суммы с учетом веса следует знать вес каждой характеристики в соответствии с предпочтением покупателя.

Операция Сортировка требует большой внимательности по выделяемым диапазонам и параметрам сортировки, иначе можно получить неправильные результаты по строкам в различных клетках. Рассмотрим Сортировку по возрастанию и убыванию.

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

Примечание. Если выделить всю таблицу и осуществить сортировку по возрастанию (убыванию), то сортировка пройдет по названиям товара по умолчанию.

1. Определите самый дешевый автомобиль.

2. Определите самый экономичный автомобиль.

3. Определите самый престижный автомобиль.

4. Определите самый лучший автомобиль, выбрав в качестве критерия сумму рангов.

5. Определите лучший автомобиль, выбрав в качестве критерия взвешенную сумму рангов.

Задание 6. Макросы

1. Создание макроса. Будем создавать макрос, который на листе Excel в ячейке К1 (или другой по выбору) формирует:

текст Фамилия Имя Отчество; шрифт Times New Roman; размер 16; цвет красный; начертание Полужирный курсив.

До создания макроса необходимо уточнить все действия, которые будут осуществляться при его создании (т.е. надо проделать все действия без записи). Будем создавать макрос для конкретной книги (можно создавать для всех книг).

Закладка Вид – Макросы – Запись макроса, в появившемся окне «Запись макроса» введите имя макроса, например свою фамилию, в поле «Сохранить в» выберите текущую книгу и дайте описание макроса в поле «Описание».

В данном окне для макроса необходимо назначить комбинацию клавиш, например Ctrl – А. Нажмите ОК.

С этого момента начинается запись макроса.

Активизируйте ячейку К1 и введите текст Фамилия Имя Отчество, а затем форматируйте содержание ячейки К1:

 шрифт Times New Roman; размер 16;

 цвет красный; начертание Полужирный курсив.

Остановите запись макроса через Вкладку Вид-Макросы.

Удалите все в ячейке К1, выделите ячейку, далее вкладка Главная –список Очистить (справа ниже кнопки автосуммы)(меню Правка-Очистить-Все).

2. Просмотр созданного макроса. Выберите Вкладку Вид – Макросы- Макросы. Откроется окно диалога «Макрос».

Выберите из списка имя созданного макроса. Нажмите кнопку Изменить. Откроется окно редактора Visual Basic. В правой части экрана будет помещен выбранный макрос. Оцените свой труд. В окне редактора можно править ошибки, но Вы закройте окно редактора без правки.

3. Запуск макроса комбинацией клавиш. Нажмите одновременно две клавиши Ctrl – A, на листе в ячейке К1 должен появиться текст Фамилия Имя Отчество в заданном формате.

Задание 7 Фильтрация

А) Выполните поиск лучшего автомобиля по всем критериям с учетом веса.

Б) Найдите автомобили, которые являются лучшими хотя бы по одной характеристике.

Сохраните документ.

Задание 8. Расширенный фильтр

Для работы с расширенным фильтром необходимо вначале набрать таблицу результатов вступительных экзаменов небольшого размера.

Абитуриенты сдают вступительные экзамены по информатике, русскому, и иностранному языку. Результаты каждого экзамена заносятся в таблицу. Образец таблицы приведён на рис. 5.3.1.

Для работы с расширенным фильтром необходимо создать вспомогательную таблицу, у которой заголовки точно соответствуют заголовкам исходной таблицы. Вспомогательная таблица создается путем копирования заголовков исходной таблицы. Т.к. список абитуриентов может пополняться, то лучше вспомогательную таблицу создавать выше основной таблицы, чтобы эта вспомогательная таблица была всегда видна (рис.5.3.2.). Во вспомогательную таблицу вводятся условия фильтрации. При вводе условий по горизонтали в строку между столбцами (по введенным условиям) выполняется логическая операция «И». При вводе условий в разные строки между столбцами (по введенным условиям) выполняется логическая операция «ИЛИ».

Затем курсор вводится в поле основной таблицы и используется вкладка Данные –Дополнительно. Появляется диалоговое окно Расширенный фильтр.

Необходимо указать в этом окне, где будет осуществлена фильтрация (фильтровать список на месте). Уточнить при необходимости исходный диапазон и указать диапазон условий. Далее кн. ОК.

В данном задании рекомендуется создать с помощью макрорекордера макросы с выводом управления на кнопки, размещенные на листе, для следующих операций:

 Сортировать таблицу по столбцу «Фамилия, имя, отчество».

 Сортировать таблицу по столбцу «Итого» (первичный ключ сортировки), а затем по столбцу «Фамилия, имя, отчество» (вторичный ключ сортировки).

 Отобразить строки таблицы с абитуриентами, которые не имеют ни одной двойки.

 Отобразить строки таблицы с абитуриентами, получившими хотя бы одну двойку.

 Отобразить строки таблицы с абитуриентами, которые не имеют двоек и получили хотя бы одну пятёрку.

Задание 9. Построение диаграмм

Основные правила:

Для создания диаграммы необходимо выделить блок данных, на основании которых строится диаграмма.

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

При выделении блоков с данными для построения диаграмм необходимо соблюдать два правила:

1. Выделенный фрагмент должен состоять из равновеликих столбцов;

2. В выделенном фрагменте не должно быть объединенных ячеек.

Откройте файл Лаб1. Выделите данные в столбцах Автор и Вклад автора (ячейки B13:B17 и F13:F17). Для построения Круговой диаграммы (рис.5) необходимо перейти на вкладку Вставка, открыть список Круговая и выбрать нужную диаграмму.

Рис.6. Гистограмма, отображающая соотношение вклада и затрат.

Выделите данные в столбцах Автор, Вклад автора и Доля затрат. Для построения Гистограммы (рис.6) необходимо перейти на вкладку Вставка, открыть список Гистограмма и выбрать нужную гистограмму.

Круговая диаграмма и гистограмма строится сразу. Иногда необходимо выделить построенную диаграмму и провести изменение размера шрифта или растянуть диаграмму для лучшего чтения данных в поле диаграммы.

Если вызвать контекстное меню в поле всей диаграммы (рис. 7), то меню предлагает три ( в предыдущих версиях было четыре шага) отдельных шага при построении диаграммы:

 Изменить тип диаграммы;

 Выбрать данные;

 Переместить диаграмму.

Второй шаг (выбрать данные) более подробно будет рассмотрен далее.

Третий шаг (переместить диаграмму) в диалоговом окне Перемещение диаграммы предлагает разместить диаграмму на отдельном листе или на имеющемся, совместно с исходной таблицей.

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

На рис.7 вверху показана появляющаяся при вызове контекстного меню панель форматирования различных областей диаграммы. При выборе из списка области диаграммы эта область выделяется в поле диаграммы для её форматирования.

Задание 10.Экспорт и импорт документов между программами Excel и Word и создание простых сайтов

Вставить через буфер в документ Word таблицу «Новые информационные технологии» с листа Excel. Вставить через буфер в документ Word диаграмму с листа Excel. Сохранить документ Word. Затем еще раз сохранить документ Word через меню Файл-Сохранить как под другим именем, выбрав тип файла Web-страница в одном файле или Web-страница. Получим сайт.

Задание 11. Выбор данных для построения диаграмм из нерегулярных таблиц

На рис.9 приведен пример нерегулярной таблицы. В таблице проведено объединение ячеек, с информацией необходимой при построении диаграмм. Для построения приведенной на рис.9 диаграммы выделяется три столбца без заголовков: Месяц, Доход и Расход.

После построения диаграммы вызывается контекстное меню и пункт Выбрать данные. В левой части удаляются Элементы легенды (ряды). Затем нажимается в левом окне кн. Добавить и в появившемся окне Изменение ряда (рис.10) в поле Имя ряда вводится щелчком мыши клетка Доход, а в поле Значения, после его очищения вводится диапазон чисел столбца Доход. Операция повторяется для клетки Расход и диапазона чисел столбца Расход. Так как при удалении рядов автоматически удаляется и содержание поля Подписи горизонтальной оси(категории), то в левое поле, нажав кн. Изменить надо ввести диапазон месяцев с Января по Декабрь.

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

Задание 12. Построение диаграмм и графиков с двумя осями

П одготовьте две таблицы Рис.11 и Рис.12. При построении диаграммы по данным таблицы рис.11 на первом шаге выберите в закладке Нестандартные Классическую смешанную гистограмму (первая ось для гистограмм, вторая для графиков).

Постройте диаграмму рис.13. Затем по данным таблицы рис.12 аналогично постройте диаграмму рис.14. Измените тип второй диаграммы с графика на гистограмму и постройте диаграмму рис.15. Примените разные цвета для отображения диаграмм и осей.

Построив приведенные диаграммы с двумя осями в программе MS Excel 2003,

сохраните файл с диаграммами на листе. Затем откройте этот файл в программе MS Excel 2007 или 2010. Вы увидите построенные ранее диаграммы. Выделите диаграмму, активируется вкладка Конструктор. Нажмите на кН. Сохранить как шаблон, откроется диалоговое окно сохранения в папке шаблонов для диаграмм Charts. Сохраните шаблон, например под именем График_две_оси. Теперь при построении подобных диаграмм на вкладке Вставка выбирается Все типы диаграмм папка Шаблоны. В этой папке выбирается нужный вам шаблон.

Задание 13. Расчет рентабельности фирмы

Используются таблицы для расчета рентабельности небольшой фирмы и доказательной иллюстрации этой рентабельности.

Первая таблица содержит сведения о расходах фирмы в первом полугодии 2011 г.

Расходы, равно как и доходы расписаны по статьям, их составляющим, а также по месяцам.

Последний столбец содержит итог по каждой статье за рассматриваемый период, а последняя

строка – итог по всем статьям расходов за месяц.

В данной таблице использованы формулы (см. Таблицу 1)

Доходы по статьям представлены во второй таблице. Уровень доходов в примере показан из расчета увеличения доходов по каждой статье на 5%.

В третьей таблице вычислена разность между затратами и доходами по месяцам.

В работе (рис. 16) представлены два графика. Первый график иллюстрирует распределение доходов, получение которых ожидается в течение первого полугодия деятельности фирмы, по статьям.

На втором графике представлено соотношение доходов и расходов по месяцам, а также по итогам полугодия. Здесь «Ряд 1»-доходы, «Ряд 2»- затраты.

Проделать все используемые действия.

Задание 14. Сортировка большого списка.

Открыть файл, созданный в задании № 8(список1). Скрыть не важные по Вашему усмотрению столбцы (например, столбец Пол и все столбцы адреса). Большие списки, а более правильно и любые, осуществляется в следующей последовательности:

 Выделить всю таблицу вместе с заголовками;

 На вкладке данные нажать Сортировка.

Появится диалоговое окно Сортировка (см. рис.). Кнопкой Добавить уровень (Удалить уровень) выбирается необходимое число столбцов для сортировки, а кнопками Повысить уровень (Понизить уровень) устанавливается порядок сортировки.

Сортировка осуществляется по правилам: сначала сортируется столбец, указанный в верхнем поле в заданном направлении сортировки (и все правые части записей), затем в отсортированном сортируется второй столбец, указанный во втором поле и т.д.

Сортировка может осуществляться по возрастанию или по убыванию Нажав в диалоговом окне кнопку Параметры можно уточнить параметры сортировки.

Выполнить следующие сортировки:

а) Провести сортировку номеров записей по убыванию;

б) Провести сортировку фамилий по возрастанию и по убыванию;

в) Провести сортировку фамилий по возрастанию, а затем осуществить группировку строк с фамилиями по алфавиту (для букв А, Б, В и Г).

Примечание: первую фамилию на выбранную букву оставлять вне поля группировки.

г) Провести сортировку трех столбцов (Фамилия, математика, информатика) по возрастанию.

Задание 15. Автофильтр с условием.

Установить Автофильтр. В списке фильтра столбца Фамилия выбрать пункт Текстовые фильтры – Настраиваемый фильтр.

а) В появившемся диалоговом окне Пользовательский автофильтр: слева вверху в поле ввести больше или равно, а справа вверху М и провести фильтрацию;

б) В диалоговом окне Пользовательский автофильтр в верхней части ввести больше или равно, а справа вверху М в центре поставить условие ИЛИ, внизу слева равно, а внизу справа ввести Ку* и провести фильтрацию;