Учебное пособие: Базовые навыки для работы в Excel

2.  Для того, чтобы в колонке С вместо символов ##### появились числа, получите указатель мыши в виде двусторонней стрелки  на границе заголовков между столбцами С и D.

3.  Щелкните дважды. Ширина столбца С изменилась в соответствии с длиной находящихся в нем данных.

4.  Уменьшите в 2 раза ширину пустого столбца B.

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

5.  Выделите группу столбцов D:N, проведя при нажатой кнопке мыши по их заголовкам.

6.  Внутри выделенного блока уменьшите в 2 раза ширину столбца E.

В столбцах D:N появятся решетки.

Рис 8.9. 

7.  Получите указатель мыши в виде двусторонней стрелки  на границе заголовков между столбцами D и E.

8.  Увеличьте ширину столбца D в три раза. Одновременно увеличится ширина всех столбцов D:N. Вместо решеток в ячейках появятся числа с большим пустым пространством перед ними.

9.  Получите указатель мыши в виде двусторонней стрелки  на границе заголовков между столбцами D и E.

10.  Сделайте двойной щелчок мыши. Ширина столбцов D:N уменьшится до ширины самых длинных данных.

11.  Снимите выделение, щелкнув вне блока.

8.6.  Как добавлять рамки и менять цвета

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

1.  Выделите ячейки С6:D8.

2.  В разделе Шрифт ленты Главная щелкните на стрелке рядом с кнопкой Границы . Откроется таблица различных рамок.

Рис 8.10. 

3.  Выберите значок Толстая внешняя граница. Снимите выделение с блока С6:D8. Вокруг выделенного блока ячеек появится толстая рамка.

4.  Строку с прибылью C21:N21 отчеркните и сверху и снизу двойными линиями.

Вопрос: Как же отчеркнуть двойными линиями, если такого варианта в таблице рамок нет? (Ответ7)

5.  Укажите ячейку C1.

6.  В разделе Шрифт ленты Главная щелкните на стрелке рядом с кнопкой Цвет заливки . Откроется палитра различных цветовых оттенков.

Рис 8.11. 

7.  Щелкните на самом светлом оттенке синего цвета, чтобы закрасить ячейки с заголовком светло-бирюзовым цветом.

8.  В ленте Разметка страницы в разделе Параметры листа снимите галочку в прямоугольнике Сетка\Вид. Сетка вокруг ячеек исчезнет с экрана.

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

Посмотрим на строку с прибылью. В нашей таблице прибыль по месяцам имеет значения, меньшие 8500 рублей и большие 8500 рублей. Как сделать так, чтобы прибыль, большая 8500 рублей, была написана символами красного цвета? Можно указать ячейки и с помощью команды Цвет текста сделать символы красными. Однако при уменьшении по каким-либо причинам размера прибыли меньше 8500 она останется красной. Как сделать так, чтобы Excel сам определял и форматировал ячейки? Инструмент для этого называется условным форматированием.

1.  Выделите ячейки D21:I21 с прибылью по 6 месяцам.

2.  В ленте Главная в разделе Стили щелкните кнопку Условное форматирование. В появившемся меню выберите пункт Правила выделения ячеек \ Больше …. Появится окно больше.

3.  В первом поле окна наберите 8500, во втором поле выберите Красный цвет.

Рис 8.12. 

4.  Нажмите кнопку ОК. Снимите выделение с ячеек прибыли.

Смотрите, прибыль, большая 8500, выделена красным цветом.

5.  Для эксперимента задайте в ячейке D11 значение прихода на 200 рублей больше (32750 рублей). Нажмите Enter.

6.  Прибыль за май превзошла 8500 и выделилась автоматически красным цветом.

7.  Задайте в ячейке D11 значение прихода опять 32550 рублей. Прибыль за май перестала быть красной.

Теперь таблица выглядит следующим образом.

Рис 8.13. 

8.8.  Шаг вперед: «Стили»

Стиль – это набор параметров форматирования ячейки под общим именем. Стиль содержит, например, шрифт и размер символов, цвет заливки, выравнивание, рамки и т.п. При изменении параметров стиля автоматически изменяется внешний вид ячеек, которые относятся к данному стилю. Однако, если пользователь задавал для ячейки свои параметры форматирования, то для внешнего вида будут использованы они. Задаваемые параметры перекрывают параметры из стиля.

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

Допустим, Вы хотите, чтобы во всех листах рабочей книги размер шрифта изменился на 14, цвет заливки стал зеленым.

1.  В ленте Главная в разделе Стили щелкните кнопку Стили ячеек. Появится таблица с готовыми стилями ячеек.

2.  В таблице со стилями щелкните правой кнопкой мыши на ячейке Обычный. В появившемся меню выберите пункт Изменить. Откроется окно Стиль.

3.  Нажмите кнопку Формат. Появится окно Формат ячеек.

4.  В окне Формат ячеек на вкладке Шрифт в поле Размер задайте 14, на вкладке Заливка в поле Цвет фона задайте зеленый цвет.

5.  Нажмите кнопку ОК в окне Формат ячеек и в окне Стиль.

6.  Посмотрите, во всех листах во всех ячейках, которые мы не форматировали, размер символов стал 14, цвет заливки стал зеленым. Представьте, что таких листов сто.

Рис 8.14. 

7.  Через раздел Стили, кнопку Стили ячеек, ячейку Обычный, пункт меню Изменить, окно Стиль, кнопку Формат, окно Формат ячеек верните старые значения размера 11, цвета фона – нет цвета.

8.  Нажмите кнопку ОК в окне Формат и в окне Стиль. Таблица вернется в нормальное состояние.

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

Диаграмма, построенная прямо на рабочем листе, называется внедренной. Если диаграмма расположена на отдельном листе, то этот лист называют листом диаграммы.

Диаграмма является объектом. Это значит, что:

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

2.  Она умеет реагировать на внешние события. Например, она самостоятельно изменяет свое изображение.

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

4.  Диаграмма является контейнером и содержит в себе другие объекты.

9.1.  Как создавать внедренные диаграммы

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

В следующем упражнении вы выберите данные для построения гистограммы и определите ее место на рабочем листе.

1.  В ленте Разметка страницы в разделе Параметры листа поставьте флажок Сетка \ Вид. В таблице появится сетка.

2.  На листе Бюджет 2008 выделите ячейки C10:I13 с названиями месяцев, приходами, затратами, выручкой за 6 месяцев. Нажмите клавишу Ctrl и, удерживая ее, выделите мышью ячейки C19:I19 с Расходами Всего.

3.  В ленте Вставка щелкните на кнопке Гистограмма и выберите самый верхний левый рисунок (Гистограмма с группировкой). Появится прямоугольная область с гистограммой.

Рис 9.1. 

В разделе Диаграммы ленты Вставка вы сможете выбрать нужный тип диаграммы. Линейчатые диаграммы обычно используют для сравнения по некоторым статьям или признакам, а гистограммы – для наблюдения изменений во времени. Круговые диаграммы наглядно отображают соотношение частей и целого. Диаграммы с областями и графики позволяют наилучшим образом изобразить непрерывное изменение величин во времени.

Убедитесь, что в главном меню появились обозначения лент Конструктор, Макет, Формат с общим заголовком Работа с диаграммами.

Рис 9.2. 

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

5.  Поместите указатель мыши в правый нижний угол диаграммы. Указатель примет форму наклонной двунаправленной стрелки. Нажмите левую кнопку мыши и, не отпуская ее, перенесите угол диаграммы в правый нижний угол ячейки I38.

6.  В ленте Конструктор в разделе Данные щелкните на кнопке Строка\столбец. Строки и столбцы в диаграмме поменяются местами.

При создании диаграммы данные автоматически разделяются на ряды данных и категории. Например, рядами данных могут стать выделенные строки, а категориями – заголовки столбцов.

В линейчатых диаграммах горизонтальная ось является осью Х, или осью категорий.

7.  В ленте Макет в разделе Подписи щелкните на кнопке Название диаграммы. Выберите пункт Над диаграммой и наберите с клавиатуры слова Бюджетный отчет. Нажмите клавишу Enter. Появится название диаграммы.

Рис 9.3. 

8.  Щелкните кнопку Названия осей и пункт меню Название основной горизонтальной оси\ Название под осью. Наберите с клавиатуры слова 2008 год. Нажмите клавишу Enter. Появится название горизонтальной оси.

9.2.  Диаграмма замечает изменения в таблице

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

1.  В ячейку С11 введите текст Приход в кассу.

2.  В ячейку D11 вместо 32550 введите приход 62550.

3.  Покиньте ячейку D11, нажав клавишу любой стрелки.

Данные в строке 11 изменятся. Диаграмма автоматически отобразит изменения. Изменится надпись Приход в кассу в легенде справа, высоты столбцов, масштаб вертикальной оси.

Рис 9.4. 

4.  Нажмите клавиши Ctrl+Z. Значения приходов в таблице и высоты столбцов в диаграмме вернутся в исходное состояние.

5.  Еще раз нажмите клавиши Ctrl+Z. Вернется надпись Приход.

9.3.  Как добавить и удалить ряд данных

В этом упражнении мы добавим на диаграмму ряд Прибыль и удалим ряд Расходы всего.

1.  В ленте Конструктор нажмите кнопку Выбрать данные. Появится окно Выбор источника данных.

Рис 9.5. 

2.  В поле Элементы легенды нажмите кнопку Добавить. Появится окно Изменение ряда.

3.  Проверьте (поставьте) курсор вставки в поле Имя ряда и отметьте ячейку C21 с заголовком Прибыль.

4.  Выделите (удалите) содержание поля Значения и выделите блок D21:I21 со значениями прибыли.

Рис 9.6. 

5.  Нажмите ОК в окне Изменение ряда и ОК в окне Выбор источника данных.

6.  В диаграмме добавятся столбцы с прибылью.

7.  Щелкните по одному из столбиков с Расходом Всего (сиреневый). У всех шести столбиков по углам появятся метки.

8.  Нажмите клавишу Del. Столбики с расходами исчезнут из диаграммы.

Рис 9.7. 

9.4.  Как увеличить ряд данных

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

1.  В ленте Конструктор в разделе Данные щелкните на кнопке Строка\столбец. Строки и столбцы в диаграмме поменяются местами.

2.  Щелкните кнопку Выбрать данные. Появится окно Выбор источника данных.

3.  В поле Элементы легенды нажмите кнопку Добавить. Появится окно Изменение ряда.

4.  Проверьте (поставьте) курсор вставки в поле Имя ряда и отметьте ячейку J10 с заголовком Всего.

5.  Удалите содержание поля Значения. Через цветастый квадрат в правой части поля Значения уменьшите окно.

6.  Выделите блок J11: J13 и с клавишей Ctrl дополнительно ячейку J11 со значениями столбца Всего.

7.  Через цветастый квадрат в правой части поля Значения увеличьте окно.

8.  Нажмите ОК в окне Изменение ряда и ОК в окне Выбор источника данных.

Рис 9.8. 

9.  В ленте Конструктор в разделе Данные щелкните на кнопке Строка\столбец.

Гистограмма примет следующий вид.

Рис 9.9. 

9.5.  Изменение диаграммы

Лучше сразу, за один раз построить нужную диаграмму. Для этого нужно заранее выразить свои желания. Однако бывают нечеткие требования и ошибки, после которых требуется исправлять диаграмму.

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

1.  Правой кнопкой мыши щелкните в один из фиолетовых столбиков с приходом.

2.  В контекстном меню выберите пункт Формат рядов данных. Появится окно Формат ряда данных.

3.  В области Заливка отметьте Сплошная заливка и через кнопку Цвет выберите зеленый цвет.

4.  Нажмите кнопку Закрыть.

Столбики с приходами окрасятся в зеленый цвет.

5.  Правой кнопкой мыши щелкните в горизонтальную ось.

6.  В контекстном меню выберите пункт Добавить основные линии сетки. Появятся вертикальные линии разметки.

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

8.  В контекстном меню выберите пункт Формат легенды. Появится окно Формат легенды.

9.  На вкладке Параметры легенды отметьте точку в кружочке Снизу. На вкладке Тень через кнопку Заготовки выберите второй образец. Нажмите Закрыть.

10.  Правой кнопкой мыши щелкните в легенду и в контекстном меню выберите пункт Шрифт. Появится окно Шрифт. На вкладке Шрифт в поле Шрифт выберите Times New Roman.

Легенда получит тень, изменит шрифт и разместится под гистограммой.

11.  Правой кнопкой мыши щелкните в белую область вокруг гистограммы.

12.   В контекстном меню выберите пункт Формат области диаграммы. Появится окно Формат области диаграммы.

13.  В области Заливка отметьте точку Рисунок или текстура.

14.  Через кнопку Текстура щелкните 13-й образец с рисунком газетной бумаги.

15.  Нажмите кнопку Закрыть.

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

Рис 9.10. 

9.6.  Построение графиков

Графики являются частным случаем диаграммы. И правила построения графиков и диаграмм одинаковы.

1.  Правой кнопкой мыши щелкните в серую область вокруг гистограммы.

2.   В контекстном меню выберите пункт Изменить тип диаграммы. Появится окно Изменение типа диаграммы.

3.  В левой панели отметьте пункт График. В правой панели отметьте первый рисунок в разделе График. Нажмите кнопку ОК. Результаты будут представлены в виде графиков.

Рис 9.11. 

9.7.  Как построить диаграмму на отдельном листе

Часто требуется создать диаграмму на отдельном листе. В следующем упражнении Вы построите круговую диаграмму на отдельном листе, используя ленту Вставка.

1.  Перейдите на лист Первый квартал и выделите диапазон ячеек C15:D18 со статьями расходов за март.

2.  В ленте Вставка в разделе Диаграммы выберите команду Круговая. В появившемся окне выберите первый рисунок (Круговая). Появится круговая диаграмма.

3.  В ленте Конструктор в разделе Макеты диаграмм выберите первый макет (Макет 1).

4.  Два раза щелкните в поле Название диаграммы и наберите Расходы за март 2008.

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

Слева от листа Первый квартал будет вставлен лист диаграммы Диаграмма1.

Рис 9.12. 

10.  Ответы

Ответ 1) В Excel 2007 Вы найдете 15 курсоров мыши:

, , , , , , , , , , , , , , .

Ответ 2) Щелкнуть по заголовку столбца С. Затем при нажатой клавише Ctrl щелкнуть по заголовку строки 3.

Ответ 3) Нулевого числа нет в месяцах.

Ответ 4)

1.  Через кнопку Office, кнопку Параметры Excel, раздел Правописание выберите команду Параметры автозамены. Появится окно Автозамена.

2.  В окне Автозамена в поле заменять введите Леот. В поле На введите Лето и щелкните две кнопки ОК.

Ответ 5) Выделите диапазон ячеек C15:I19. 1-й щелчок мыши. Этот диапазон ячеек содержит числовые данные по расходам, пустую строку и пустую колонку для итоговых сумм. Щелкните по кнопке Автосумма, результаты суммирования по строкам появятся в ячейках I15:I19, а суммы по столбцам в ячейках C19:I19. 2-й щелчок мыши.

Ответ 6) Прибыль = Полная выручка-Расходы Всего (=C13-C19, =D13-D19, …).

Рис 10.1.  Раздаточный материал

1.  Информатика: практикум по технологии работы на компьютере/ Под ред. Н.В.Макаровой. – М.: Финансы и статистика, 1997. – 384С.

2.  Microsoft Office 97. Шаг за шагом (книга и компакт-диск): Практ. пособ./ Пер. с англ. – М.: Издательство ЭКОМ, 1998. – 800 с.

3.  Практикум по экономической информатике: Учебное пособие. Часть 1. Под ред. Шуремова Е.Л., Тимаковой Н.А., Мамонтовой Е.А. – М.: Изд-во «Перспектива», 2000. – 300 c.

4.  Карлберг К. Бизнес-анализ с помощью Excel 2000./ Пер.с англ.:- М.: Издательский дом «Вильямс», 2001. – 480 с.

5.  Попов А.А. Excel: практическое руководство. - М.: ДЕСС КОМ, 2001. – 301 с.

6.  Microsoft Excel. Версия 2002. Шаг за шагом: Практ. пособие/ Пер. с англ. М.: Издательство ЭКОМ, 2003. – 368 с.

7.  Забиралов С.В., Киселев В.Г., Усков А.В. Word: шаг за шагом: Уч.-метод. пособие.- Н.Новгород: Изд. Нижегородского ун-та, 2005. – 85c.

8.  Паненко И.Г. Офисные программы. Шаг за шагом. - М.: Эксмо, 2007. – 384 с.

9.  Стоцкий Ю., Васильев А., Телина И. Office 2007. Самоучитель. – Спб.: Питер, 2007. – 524 с.