Лабораторная работа №2

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

Чтобы выбрать типы диаграмм, представленные в палитре кнопки панели инструментов, выберите команду Диаграмма > Тип диаграммы (Chart > Chart Type). Откроется окно диалога, первая вкладка которого предлагает на выбор больше сотни стандартных вариантов диаграмм, а вторая дает пользователю возможность сконструировать свой собственный тип диаграммы.

Задание:

1. 
    Создайте следующую таблицу:
   

2. 
   Выберите команду Вставка ^ Рисунок > Диаграмма (Insert > Picture > Chart). В документе Word появится диаграмма и откроется окно таблицы данных Microsoft Graph XP. Информация из таблицы Word будет перенесена в окно Graph XP, а меню и панели инструментов Word заменятся на соответствующие компоненты модуля Graph, как показано на рис. 1.
   

3. 
   Щелкните на пустой области документа Word. Окно данных, панели инструментов и меню модуля Graph XP исчезнут, а ниже таблицы Word разместится диаграмма, представляющая данные этой таблицы в графической форме.
   

Скорее всего, вас не устроит та диаграмма, которая генерируется по умолчанию. Модуль Microsoft Graph предоставляет широчайшие возможности форматирования. Вы можете выбирать тип диаграммы, изменять цвет рядов данных, добавлять заголовки, перемещать легенду, настраивать оси, их шкалы и метки делений, добавлять подписи данных и изменять множество дополнительных параметров, влияющих на способ представления данных. В этом упражнении на примере диаграммы, построенной на базе таблицы Word, будут рассмотрены лишь некоторые из доступных приемов форматирования.

1. 
   Чтобы модифицировать диаграмму, нужно снова открыть модуль Microsoft Graph. Для этого дважды щелкните на диаграмме.
   
2. 
    Щелчком на кнопке Режим таблицы скройте таблицу данных Graph. Сейчас мы не будем изменять данные. Таблица данных Graph похожа на лист Excel. Если вам придется вручную вводить данные для диаграмм Graph, пользуйтесь приемами, изложенными в упражнениях занятия 9.
   
3. 
    В панели инструментов Стандартная раскройте палитру кнопки типа диаграммы и выберите подходящий тип, например График
   

4. 
   Легенда диаграммы расположена неудобно, давайте переместим ее вниз. Для этого щелкните на легенде правой кнопкой мыши, выберите в контекстном меню команду Формат легенды и на вкладке Размещение открывшегося окна диалога выберите положение переключателя Внизу, посередине. Затем щелкните на кнопке ОК.
   
5. 
    Захватите мышью правый нижний маркер габаритного прямоугольника диаграммы и перетащите его вправо вниз, чтобы сделать диаграмму немного крупнее, как показано на рис. 2.
   

6. 
    Дважды щелкните на числах меток вертикальной оси графика. В открывшемся окне диалога разверните вкладку Шкала показанную на рис. 3.
   

6. 
    В одноименные поля после флажков введите числа 80 и 20 соответственно.
   
7. 
    Щелкните на кнопке ОК. Четыре другие вкладки окна диалога форматирования осей позволяют настраивать вид осей, шрифт меток делений, числовой формат меток и режим их выравнивания.
   
8. 
    Чтобы убрать серый фон графика, выберите команду Диаграмма > Параметры диаграммы, которая открывает окно диалога параметров диаграммы. Шесть вкладок этого окна содержат следующие параметры:
   

• 
   Заголовки— названия диаграммы, вертикальных и горизонтальных осей;
  
• 
   Оси— режимы отображения осей категорий и значений;
  
• 
   Линии сетки — флажки включения и отключения вертикальных и горизонтальных линий сетки фона диаграммы;
  
• 
   Легенда— режим отображения и способ расположения легенды диаграммы;
  
• 
   Подписи данных— переключатель, дающий возможность выводить различные подписи рядом с маркерами данных;
  
• 
   Таблица данных— флажки настройки режима вывода таблицы отображаемых данных под горизонтальной осью диаграммы.
  

11. 
     На вкладке Линии сетки , показанной на рис. 4, сбросьте флажок основные линии в разделе Ось Y.
    
12. 
    Установите одноименный флажок в разделе Ось Х.
    
13. 
     Щелчком на кнопке ОК закройте окно диалога. Горизонтальные линии сетки будут заменены на вертикальные.
    
14. 
     Щелкните на области построения диаграммы. В меню Формат выберите первую команду. Ее название начинается со слова Выделенный, после которого следует название выделенного элемента. В данной ситуации это будет команда Выделенная область построения диаграммы .
    

15. 
     В открывшемся окне диалога выберите положение Прозрачная переключателя Заливка чтобы отменить заливку диаграммы. Затем щелкните на кнопке ОК.
    

16. 
    Чтобы выйти из режима редактирования диаграммы и закрыть модуль Microsoft Graph, щелкните на свободном пространстве документа Word. Теперь таблица и график будут выглядеть так, как показано на рис. 5
    
17. 
    Добавьте в конец таблицы еще один столбец и заполните его ячейки.
    
18. 
    Залейте фон первой строки и первого столбца таблицы желтым цветом.
    
19. 
    Двойным щелчком на диаграмме запустите Microsoft Graph.
    
20. 
     Дважды щелкните на подписях горизонтальной оси и в открывшемся окне диалога измените ориентировку подписей, чтобы их текст выводился вертикально.
    
21. 
     Добавьте заголовки диаграммы и осей.
    
22. 
    Рассортируйте строки таблицы по возрастанию чисел в столбце Февраль.
    
23. 
    В области легенды выделите маркер ряда данных Трубы, дважды щелкните на нем и в открывшемся окне диалога измените цвет маркера на черный. Повторите эту операцию для всех рядов данных.
    

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

Контрольное вопросы:

1. 
   Как обновить диаграмму, чтобы в ней появились данные добавленного столбца?
   
2. 
   Как автоматически рассортировать табличные данные?
   
3. 
   Где назначаются заголовки элементов диаграммы?
   

Лабораторная работа №3

Внешний вид MS Excel. Ввод и редактирование данных. Вставка формул и редактирование данных.

Для удаления листа необходимо его выделить и выбрать Правка + Удалить либо щелкнуть правой кнопкой мыши на ярлыке листа и выбрать команду «Удалить».

Маркер заполнения - это небольшой черный квадрат в углу выделенного диапазона. Попав на маркер заполнения, указатель мыши принимает вид черного креста .

Автозаполнение - это функция, которая помогает быстрее вводить данные. При перетаскивании маркера заполнения ячейки может происходить не только копирование одних и тех же значений. Например, введите в какую-либо ячейку число 1, а в соседнюю справа от нее - число 2. Затем отметьте обе ячейки и протяните маркер заполнения вдоль строки. Появится ряд значений: 1,2,3,4…

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

Чтобы выделить диапазон по его имени, надо щелкнуть стрелку вниз в поле «Имя» в строке формул и выбрать имя диапазона из списка.

Обратите внимание, что в диалоговом окне «Имя» адреса ячеек или диапазонов появляются со знаком $. Это так называемые абсолютные ссылки, которые не изменяются в процессе различных операций Excel.

Вставка строк и столбцов выбрать Вставка + Строки или Вставка + Столбцы.

Вставленные строки или столбцы будут иметь то же форматирование, что и выделенные до этого ячейки.

Автосуммирование и автовычисления

Так как одной из наиболее часто употребляющихся функций является СУММ, в Excel предусмотрен быстрый способ ее ввода:

1. 
   Выделить ту ячейку, в которую необходимо вставить сумму. Лучше, если она расположена в конце строки или столбца данных - это поможет Excel «догадаться», какие ячейки необходимо просуммировать.
   
2. 
   Щелкнуть кнопку «Автосумма» на стандартной панели инструментов. В выделенную ячейку будет введена функция СУММ и адрес диапазона левее или выше ячейки.
   
3. 
   Если выбранный Excel диапазон будет неверным, то необходимо исправить формулу «вручную» в строке формул, либо перетащить курсор мыши через необходимый диапазон.
   
4. 
   Нажать клавишу Enter либо щелкнуть кнопку Enter в строке формул.
   

Работа с именами ячеек и диапазонов

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

Присваивание имени диапазону ячеек.

1. 
   Указать ячейку, либо выделить диапазон, либо выделить область несмежных ячеек.
   
2. 
   Установить указатель на поле имен в левой части строки формул и нажать кнопку мыши.
   
3. 
   Ввести имя.
   

1. 
   Выделить область, в которой следует присвоить имена строкам или столбцам. Выделенная область должна содержать строку или столбец заголовков.
   
2. 
   Выбрать Вставка, Имя, Создать.
   
3. 
   В появившемся окне в группе флажков «По тексту» указать расположение заголовков, из которых следует создать имена.
   

Чтобы вставить имя в формулу, его предварительно следует присвоить.

Если имя присвоено, то при вводе или исправлении формулы нужно выбрать Вставка, Имя, Вставить, а затем выбрать необходимое имя из списка.

Правка имен ячеек, диапазонов, формул и ссылок.

1. 
   Необходимо выбрать Вставка + Имя + Присвоить
   
2. 
   Выбрать необходимый элемент из списка
   
3. 
   Для правки ссылки исправить ее в поле «Формула»
   
4. 
   Для правки имени надо ввести новое имя и нажать кнопку «Добавить». После этого старое имя можно удалить.
   

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

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

Для установки любых параметров границ ячеек и необходимо выбрать Формат, Ячейки и щелкнуть вкладку «Граница». Затем с помощью соответствующих кнопок установить необходимые параметры границ, в том числе тип линии, цвет и с каких сторон ячеек будут установлены границы. При этом в средней части окна можно увидеть, как будут выглядеть ячейки после изменения их границ.

Для установки любых параметров заливки надо выделить необходимые ячейки, а затем выбрать Формат, Ячейки и щелкнуть вкладку «Вид». На этой вкладке можно установить цвет фона и узор для выделенных ячеек. При этом в поле «Образец» можно увидеть, каким будет фон после изменения параметров.

Автоформат и кнопка «Формат по образцу»

Автоформат предоставляет 16 форматов таблиц, которые можно применить к диапазону ячеек. Для применения автоформата необходимо:

1. 
   Выделить диапазон ячеек.
   
2. 
   Выбрать Формат, Автоформат. На экране появится окно «Автоформат», в левой части которого расположен список форматов, а в поле «Образец» виден внешний вид будущего формата.
   
3. 
   Необходимо отметить нужный формат в списке.
   
4. 
   Чтобы исключить из выбранного формата некоторые элементы, можно щелкнуть кнопку «Параметры».
   
5. 
   Нажать Ok.
   

Очень удобно для копирования форматов использовать кнопку «Формат по образцу» (в виде «кисточки») на панели стандартной инструментов:

1. 
   Выделить ячейку (или ячейки) с форматом, который необходимо скопировать и вставить на новом месте.
   
2. 
   Щелкнуть кнопку «Формат по образцу». Указатель мыши примет вид «кисточки» с расположенным рядом с ней знаком плюс.
   
3. 
   Перетащить курсор мыши через ячейки, к которым необходимо применить скопированный формат.
   

Условное форматирование

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

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

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

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

Изменение ширины столбцов и высоты строк.

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

Для точной установки высоты строк следует выбрать Формат, Строка, Высота. При этом следует иметь в виду, что высота строки измеряется в пунктах (1/72 дюйма) в диапазоне от 0 до 409. (Если установить высоту строки равной 0, то она будет скрыта.)

Для точной установки ширины столбцов следует выбрать Формат, Столбец, Ширина и в появившемся окне ввести число в диапазоне от 0 до 255 (Это число приблизительно равно количеству символов стандартного шрифта, которое поместиться в ячейке указанной ширины). Если ввести ширину столбца раной 0, то столбец будет скрыт.

Ввод формул

Чтобы ввести формулу с клавиатуры, надо выполнить следующие действия:

1. 
   Щелкнуть ячейку, в которую необходимо ввести формулу
   
2. 
   Набрать знак равенства (=)
   
3. 
   Набрать формулу. Она появится в строке формул.
   
4. 
   Нажать Enter или щелкнуть «галочку» в строке формул. Excel вычислит результат.
   

1. 
   Запустите Microsoft Excel:
   

2. 
   Рассмотрите внешний вид окна Microsoft Excel.
   
3. 
   В ячейку С10 введите цифру «1», а в ячейку D10 цифру «2» Выделите диапазон С10:D10 и протяните маркер заполнения до ячейки Н10.
   
4. 
   В ячейку С10 введите слово «Январь» и протяните маркер заполнения до ячейки Н10.
   
5. 
   Используя команду Сервис + Параметры + Списки создайте новый список со следующими элементами:
   

Тяжкие телесные повреждения

Разбой

Грабеж

Мошенничество

Хулиганство

Кража

6. 
   В ячейку В11 введите слово «Убийства» и протяните маркер заполнения вниз до ячейки В18.
   
7. 
   В ячейку В19 введите «Итого», в ячейку В20 – «Из них раскрыто».
   
8. 
   В ячейку В21 скопируйте текст из ячейки В11 и вновь протяните марке заполнения вниз до ячейки В28.
   
9. 
   В ячейку В29 введите «Итого».
   
10. 
    Выделите диапазон В6:В8 и импортируйте его данные в список.
    
11. 
    В ячейку G6 введите «Темпы роста» и перетащите маркер заполнения в ячейку G8.
    
12. 
    В диапазоне С11:Н15 введите следующие данные:
    

13. 
    В ячейке С16 введите число «5», а в ячейке D16 число «7», выделите диапазон С16:D16 и протяните маркер заполнения до ячейки Н16.
    
14. 
    В ячейке С17 введите число «8», а в ячейке D17 число «10», выделите диапазон С17:D17 и протяните маркер заполнения до ячейки Н17.
    
15. 
    В ячейке С18 введите число «12», а в ячейке D18 число «14», выделите диапазон С18:D18 и протяните маркер заполнения до ячейки Н18.
    
16. 
    В ячейку I10 введите слово «Всего».
    
17. 
    Используя кнопку Автосумма на панели инструментов заполните столбец «Всего» для заполненных строк таблицы.
    
18. 
    В стоке «Итого» просчитайте сумму по столбцам.
    
19. 
    Перед столбцом А вставьте дополнительный столбец. Переставьте столбцы В и А. В результате на экране должна получиться следующая картина (см рисунок 3)
    
20. 
    Диапазону С11:С29 присвойте имя Правонарушения.
    
21. 
    В диапазоне С10:I19 каждой строке и столбцу присвоить имена по заголовкам верхней строки и левого столбца.
    
22. 
    Выделите блоки с помощью поля имени и с помощью функциональной клавиши F5.
    

Рисунок 3 Внешний вид окна документа

23. 
    После Лист3 добавьте новый лист.
    
24. 
    Переименуйте Лист1 и присвойте ему имя «Правонарушения 2004».
    
25. 
    Переместите Лист2 после Лист4.
    
26. 
    Удалите Лист3.
    

27. 
    Сохраните файл под именем Урок1 Excel _ФИ.
    
28. 
    В ячейку В4 введите функцию СЕГОДНЯ.
    
29. 
    В ячейку К10 введите слово «Среднее», в ячейку L10 - слово «Максимальное», в ячейку М10 введите слово «Минимальное».
    
30. 
    В столбце К рассчитайте среднее значение цифр соответствующей строки с января по июнь (функция СРЗНАЧ).
    
31. 
    В столбцах L и M рассчитайте максимальное (функция МАКС), минимальное (функция МИН) значение цифр соответствующей строки с января по июнь, используя имена диапазона ячеек.
    
32. 
    В ячейку D21 введите формулу = D11*D7 и не закрывая ячейки измените тип ссылки в ячейке D7 на абсолютную.
    
33. 
    Скопируйте содержимое данной ячейки по вертикали до ячейки D28.
    
34. 
    Каждую ячейку в полученном вертикальном столбце скопируйте по горизонтали до столбца I.
    
35. 
    Рассмотрите, как в формулах изменяются относительные и абсолютные ссылки на ячейки при копировании.
    
36. 
    Для полученных данных рассчитайте строку Итого, столбцы Всего, Среднее, Максимальное, Минимальное.
    
37. 
    В ячейку С30 введите функцию расчета количества непустых ячеек в диапазоне С1:С29 (функция СЧЕТЗ).
    
38. 
    В ячейку D30 введите функцию расчета количества ячеек с числами в диапазоне D1:D20 (функция СЧЕТ).
    
39. 
    На Листе 2 выделите диапазон I7:K7 и поверните текст на 90⁰ и выровняйте его по центру по горизонтали и по вертикали.
    
40. 
    На листе Правонарушения 2004 выделите диапазон D11:J18 и установите для чисел пользовательский формат ###0"штук". Если в ячейках появятся значки ####, то измените ширину столбцов.
    
41. 
    Выделите диапазон D10:J10 и поверните текст в этих ячейках на 45°.
    
42. 
    Для диапазонов C6:D8 и С10:М29 установите вокруг них толстые рамки.
    
43. 
    Для диапазона J11:J29 установите условное форматирование; выделите красным цветом и полужирным шрифтом те ячейки, значения которых больше или равно 90.
    

1. 
   Как запустить программу Microsoft Excel?
   
2. 
   Какие существуют способы заполнения и редактирования ячеек?
   
3. 
   Как выделить смежные и несмежные диапазоны?
   
4. 
   Как создать и импортировать списки?
   
5. 
   Как производится автосуммирование строк и столбцов?
   
6. 
   как перейти между листами в одной рабочей книге?
   
7. 
   Какие существуют способы ввода формул в ячейку?
   
8. 
   Как в ячейку ввести функцию?
   
9. 
   Как задать автоформат выделенному диапазону ячеек?
   
10. 
    Какие существуют способы форматирования ячеек?
    
11. 
    Как наложить пользовательский формат на ячейку?
    
12. 
    Как задать условное форматирование для выделенного диапазона ячеек?
    
13. 
    Как в формуле сделать ссылку на другой лист?
    
14. 
    Как в формулах обозначаются абсолютные и относительные ссылки?