7.2. Понятие электронной таблицы ms Excel

7.2.1. Осуществите запуск программы MS Excel командами: Пуск > Программы > MS Office > Microsoft Excel 2007.

В окне Excel сразу откроется новая рабочая книга. По умолчанию книга Excel имеет три листа со стандартными именами. Трех листов может не хватить для всех данных, а стандартные названия листов никак не раскрывают их значение.

1.2. Щелкните правой кнопкой мыши на ярлычке Лист1.

1.3. Выберите в контекстном меню команду Переименовать.

1.4. Введите новое имя Сальдо начальное.

1.5. Переименуйте Лист2 в Приход, Лист3 в Расход.

1.6. Чтобы добавить еще один пустой лист, щелкните на кнопке Вставить лист (Рис.7.2.1). Назовите новый лист Сальдо конечное.

1.7. Опробуйте различные способы выделения ячеек и групп ячеек листа вашей рабочей книги:

• Щелчок в ячейке выделяет ее.

• Чтобы выделить прямоугольную область (диапазон ячеек), щелкните в ее угловой ячейке, нажмите клавишу Shift и щелкните в ячейке противоположного угла области, либо перетащите мышь по диагонали от первого угла области до второго.

• Строка или столбец выделяются щелчком на кнопке заголовка строки или столбца (Рис.7.2.1.);

• Чтобы выделить несколько строк или столбцов, перетащите мышь по их заголовкам, или щелкните на первом заголовке, нажмите клавишу Shift и щелкните на последнем заголовке группы.

• Нажав клавишу Ctrl и щелкая в ячейках листа, можно выделить несколько разрозненных ячеек.

• Чтобы выделить весь лист, щелкните на кнопке листа, расположенной на пересечении заголовков строк и столбцов (Рис.1.1.).

Рис.7.2.1. Лист Excel

1.8. Ввод информации – это один из первых шагов построения электронной таблицы. На первом листе рабочей книги создадим таблицу Мониторы, приведенную ниже (Рис.7.2.2).

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

Введите в ячейку А1 текст Склад №1. Мониторы. По окончании ввода текста или числа ячейка находится в режиме редактирования содержимого. Многие команды для работы с ячейками в этом режиме недоступны. Чтобы выйти из режима редактирования, щелкните на кнопке Enter.

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

1.9. Текст заголовка не влезает в одну ячейку, поэтому следует объединить несколько ячеек. Выделите ячейки с А1 по F1.

Рис.7.2.2 Таблица Склад №1. Мониторы.

1.10. Щелкните на кнопке Объединить и поместить в центре, расположенной на вкладке Главная в группе Выравнивание.

1.11.Заполните оставшиеся ячейки таблицы по образцу (Рис.7.2.2).

2. Формулы в Excel.

Все вычисления в Excel выполняются с помощью формул, которые можно вводить в любые ячейки листа. Если содержимое ячейки начинается со знака равенства (=), Excel полагает, что вслед за этим знаком идет формула, и пытается выполнить указанную операцию. Когда это удается, в ячейку с такой формулой выводится результат расчета. Если что-то не так, появляется сообщение об ошибке. Сама формула отображается в ячейке только в том случае, если ячейка находится в режиме редактирования текста (то есть только после двойного щелчка в ней). Если ячейка просто выделена, то ее формула выводится в строке формул (Рис.7.2.1.).

2.1. Щелкните в ячейке D3 и введите формулу:

= С3*1,2

С3 – это ссылка на соответствующую ячейку. Ссылку на ячейку можно задать разными способами.

Во-первых, адрес ячейки можно ввести вручную.

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

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

2.2. Щелкните в ячейке F3 и введите формулу:

= D3*E3

2.3. Теперь эти формулы необходимо скопировать в ячейки D4 – D15 и F4 – F15 соответственно. Рассмотрим один из методов копирования – метод перетаскивания:

• Выделим ячейку D3.

• Установим указатель мыши на маленький черный квадратик в правом нижнем углу ячейки – маркер заполнения (Рис.7.2.3.);

• нажмем левую кнопку мыши и растянем рамку вниз до ячейки D15.

На первый взгляд, кажется, что в ячейки D4 – D15 должна дублироваться формула ячейки D3. Но это не так. Excel поддерживает механизм относительных ссылок на ячейки. Когда формула переносится на одну клетку вниз, точно так же смещаются все ссылки этой формулы (это верно и для смещения формулы в любых других направлениях на любое число ячеек).

2.4. Аналогично поступим для копирования содержимого ячейки F3.

Рис.7.2.3. Копирование формулы

2.5. Excel поддерживает множество стандартных математических функций, которые можно вставлять в формулы.

В ячейку F16 нужно ввести функцию суммирования. Для этого выполните нижеперечисленные пункты.

• Щелкните по ячейке F16.

• Затем по кнопке Вставить функцию (Рис. 7.2.1).

• Выберите категорию Математическиеи функциюСУММ, щелкнитеОК.

• Открывшееся диалоговое окно следует, удерживая мышью за строку заголовка, передвинуть так, чтобы оно не перекрывало таблицу, и выделить блок ячеек F3 – F15, затем щелкнуть ОК.

После этого в ячейке F16 будет автоматически подсчитана сумма ячеек столбца Сумма.

2.6. Щелкните в ячейке F5 и измените находящееся в ней число.

Excel автоматически скорректирует значение соответствующей ячейки в столбце Сумма и итоговую сумму в ячейке F16.

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

2.7. К данному моменту мы составили таблицу, отражающую сальдо начальное. Теперь создадим таблицу прихода. Для этого:

• Необходимо выделить всю таблицу.

• Скопировать выделенную область, вызвав контекстное меню.

• Перейти на лист Приход.

• Вставит скопированную таблицу в ячейки А1:F16 листа Приход.

• На листе Приход измените значения колонки Количество на свое усмотрение.

2.8. По аналогии необходимо создать таблицу расхода, также изменив значения колонки Количество.

2.9. Теперь создадим итоговый лист сальдо конечного.

• Скопируйте таблицу на лист Сальдо конечное, как описано в пункте2.7.

• Теперь в столбце Количествов ячейке соответствующей первому по списку товару (в нашем случае это ячейкаЕ3), введите следующую формулу, которая создаст связь между всеми листами:

= Сальдо начальное!E3 + Приход!E3 - Расход!E3

Для создания связи введите в ячейку E3знак равенства (=), затем щелкните на ярлычке листаСальдо начальное, выделите ячейкуE3, введите знак суммы (+), затем щелкните на ярлычке листаПриход, выделите ячейкуE3, затем знак разности (-), потом щелкните на ярлычке листаРасход, выделите ячейкуE3, нажмите клавишуEnter.

Формула связи запишется в ячейку автоматически. При изменении содержимого соответствующей ячейки на листе –источнике, ячейка, содержащая связь с ней, также изменит свое содержимое.

• Скопируйте содержимое ячейки E3 листаСальдо конечноев ячейкиE4:E15.

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

3.1.На листеСальдо начальноещелкните в одной из ячеек шапки таблицы (А2 - F2).

3.2.На вкладкеДанныев группеСортировка и фильтрщелкните на кнопкеФильтр.

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

3.3.Нажмите на такой стрелке в столбцеФирма. Уберите галочкуВыделить все. Установите галочку напротивSamsung. Таблица примет вид, как на рисунке7.2.4.

Рис.7.2.4 Результат фильтрации.

3.4.Нажмите на стрелке столбцаЦенаи выберите пунктЧисловые фильтры, а в немменьше или равно…

3.5.В открывшемся окне задайте условие фильтрации, при котором будут отображаться мониторы, цена которых не превышает4000 (Рис. 7.2.5) и нажмитеОК.

Рис 7.2.5 Окно Пользовательский автофильтр.

3.6.Отмените фильтрацию, нажав на кнопкуФильтрна вкладкеДанные.

3.7. Выделите ячейкиА2:F15.

3.8.На вкладкеДанныев группеСортировка и фильтрщелкните на кнопкеСортировка.

3.9.В открывшемся окне установите «Сортировать по» - «Количество», «Порядок» - «По возрастанию».

Строки вашей таблицы переместятся в соответствии с заданным условием (Рис. 7.2.6).

Рис 7.2.6 Результат сортировки

3.10.Произведите сортировку по столбцуФирма, чтобы вернуть таблице прежний вид.

3.11.Выделите ячейкиА2:F15.

Рис. 7.2.7 Подведение промежуточных итогов.

3.12.На вкладкеДанныев группеСтруктуращелкните на кнопкеПромежуточные итоги.

3.13.В открывшемся окнеПромежуточные итогиустановите: в строке «При каждом изменении в» - «Фирма», в строке «Операция» - «Сумма», в «Добавить итоги по» - пометьте галочкой «Сумма», а также пометьтеИтоги по данным, щелкнитеОК. Ваш рабочий лист примет вид как на рисунке7.2.7.

3.14.В процессе подведения промежуточных итогов автоматически структурируется лист. На полях рабочего листа слева теперь расположены вертикальные линии с рядом кнопок уровней, щелкая на этих кнопках, можно скрывать детали листа. Щелкните на кнопке первого уровня – таблица содержит только общий итог, а на месте кнопок уровня осталась только одна (+). Щелкните на ней для возврата данных. Пощелкайте на всех кнопках второго уровня. Понаблюдайте за изменениями на рабочем листе.

3.15.Выделите ячейкиА2:F19. Щелкните на кнопкеПромежуточные итоги, затем на кнопкеУбрать все.

4. Форматирование листа Excel.

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

4.1. Выделите прямоугольную область А2:F16 таблицы Сальдо начальное и щелкните на кнопке Форматировать как таблицу, расположенной на вкладке Главная в группе Стили.

4.2. В открывшейся коллекции щелкните на образце Темный 10. ПрограммаExcel сама оформит таблицу выбранным стилем.

4.3. Оформим строку итогов в соответствие с заголовками столбцов, выделите ячейки А16:Е16 и щелкните на кнопке Формат, расположенной на вкладке Главная в группе Ячейки.

4.4. В появившемся меню выберите команду Формат ячеек.

4.5. В появившемся диалоговом окне Формат ячеек выберите вкладку Заливка. Щелкните на квадратном образце лилового цвета, сходного с цветом фона заголовков столбца.

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

4.7. Выделите ячейки А3:В15, затем щелкните в них правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек.

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

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

4.10. Щелкните на кнопках Полужирный и Курсив в группе Шрифт на вкладке Главная.

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

4.12. Нажмите кнопку мыши и перетащите границу столбцов влево, уменьшив этим ширину столбца С.

4.13. Часть заголовка столбца С пропала. Поместите указатель мыши на границу второй и третьей строки и перетащите границу строк вниз так, чтобы заголовок снова был полностью виден.

4.14. Выделите ячейки А3:F15. Щелкните на кнопке Главная > Ячейки > Формат и в появившейся коллекции выберите пункт Высота строки.

4.15. В открывшемся диалоговом окне введите число 17 и щелкните на кнопке ОК.

4.16. Выделите ячейки А2:F16.

Рис. 7.2.8 Оформленная таблица.

4.17. Щелкните на кнопке Главная > Ячейки > Формат и в появившейся коллекции выберите пункт Автоподбор ширины столбца. Эта операция максимально сузит столбцы, но лишь до такого размера, чтобы все данные свободно помещались в ячейках таблицы.

4.18. Выделите ячейку F7. И щелкните на кнопке Увеличить разрядность, расположенной на вкладке Главная в группе Число. Эта кнопка добавляет один знак в дробной части числа.

4.19. Выделите ячейку F10. И щелкните на кнопке Уменьшить разрядность. Эта операция убирает один знак дробной части и округляет число. После всех проделанных манипуляций таблица будет выглядеть как на рисунке 7.2.8.

5. Создание диаграммы.

А теперь изучим приемы построения диаграмм, иллюстрирующих данные листа Excel. Построим диаграмму на основе данных столбца Количество.

5.1.Чтобы создать диаграмму, следует сначала указать источник данных. Для этого выделите ячейкиВ3:В15листаСальдо начальное.

5.2.Нажмите клавишу Ctrl и не отпуская ее выделите диапазон ячеек Е3:Е15.

5.3. Тип диаграммы можно выбрать в коллекциях, вызываемых с помощью кнопок в группе Диаграммы на вкладке Вставка. Выберем образец Объемная разрезанная круговая. Щелкните на кнопке ОК.

5.4.После выбора диаграммы на листе появится область диаграммы с вашей диаграммой.

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

Чтобы добавить название диаграммы на вкладке Макетв группеПодписищелкните на кнопкеНазвание диаграммы.

5.5.В появившейся коллекции выберитеНад диаграммой.

5.6.Чтобы перейти в режим редактирования надписи, щелкните на ней дважды.

5.7.Сотрите старую надпись и введите текстСклад №1. Мониторы. Количество.

5.8.Чтобы завершить редактирование текстового элемента щелкните мышью за его пределами.

5.9.Чтобы поместить диаграмму на отдельный лист, на вкладкеКонструкторв группеРасположениещелкните на кнопкеПереместить диаграмму.

5.10.В появившемся диалоговом окнеПеремещение диаграммыустановите переключательНа отдельном листе.

5.11.В текстовом поле имени названия листа введитеДиаграмма(Рис. 7.2.9).

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

5.13.В поле со спискомРазмер шрифта, которое расположено на вкладкеГлавнаяв группеШрифт, установите размер шрифта равный28.

5.14.Добавим на нашей диаграмме подписи рядов данных. Для этого щелкните на кнопкеПодписи данных, расположенной в группеПодписина вкладкеМакет.

5.15.В раскрывшейся коллекции выберитеДополнительные параметры подписей данных…

5.16.В открывшемся диалоговом окнеФормат подписей данныхна вкладкеПараметры подписив разделеВключить в подписиустановите флажокДолии сбросьте флажкиЛинии выноскииЗначения.

Рис. 7.2.9 Выбор расположения диаграммы.

5.17. В разделе Положение подписи выберите вариант У вершины, внутри.

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

5.19. Щелкните на подписях ряда данных правой кнопкой мышии выберите в контекстном меню команду Шрифт.

5.20. В появившемся диалоговом окне в поле Размер введите число 20. В раскрывающемся списке Цвет выберите белый цвет. Щелкните на кнопке ОК.

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

Щелкните правой кнопкой мыши в области легенды. Появится контекстное меню легенды, а также мини-панель, с помощью которой можно выполнить форматирование текста.

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

После этого диаграмма должна выглядеть примерно так, как показано на рисунке 7.2.10

Рис. 7.2.10 Объемная разрезанная круговая диаграмма.

Задания для самостоятельной работы.

1. Переключитесь на Лист 6.

2. Измените название этого листа на Календарь.

3. Введите в ячейки А3:А9 символы Пн, Вт, Ср, Чт, Пт, Сб, Вс.

4. В прямоугольную область В3:F9 введите числа календаря на текущий месяц.

5. В строке 1 введите заголовок Календарь на текущий месяц, объедините для него несколько ячеек и выровняйте по центру.

6. Добавьте обрамление жирной линией вокруг блока ячеек В3:F9.

7. Отделите границей столбец А от столбца В.

8. Залейте фон ячеек строк 8 и 9 красным цветом, а шрифт текста этих строк выделите жирным начертанием.

9. Щелкните в ячейке G3, введите формулу, вычисляющую среднее значение чисел строки 3 календаря, и нажмите клавишу Enter.

10. В ячейку H3 выведите целую часть этого среднего значения.

11. Продублируйте две полученные формулы во всех строках календаря.