Лабораторные работы/ Практические занятия. - Создание, заполнение, оформление и редактирование электронной таблицы

11 12 13 14 15 16 17

- Создание, заполнение, оформление и редактирование электронной таблицы.

- Проведение расчетов и поиска информации в электронной таблице с использованием формул, функций и запросов.

- Проведение расчетов с использованием формул, функций. Абсолютная и относительная адресация.

- Работа с графическими возможностями электронной таблицы…

Тема « Создание, заполнение, оформление и редактирование электронной таблицы. »

Цель: рассчитать экономическую эффективность использования ремонтных мастерских.

Создайте таблицу и заполните ее данными:

Введите формулы в ячейки столбца В, помеченные знаком?, затем скопируйте их:

Ячейки	Формулы	Область копирования
В8	=В5*300	С8 – F8
В12	=B10-B11	C12 – F12
В14	=B9/B7	C14 – F14
В15	=B9/B6	C15 – F15
В16	=B10/B9	C16 – F16
В18	=B10/B7*1000	C18 – F18
В19	=B10/B6*1000	C19 – F19
В20	=B10/B8*1000	C20 – F20
В22	=B12/B7*1000	C22 – F22
В23	=B12/B6*1000	C23 – F23
В24	=B12/B8*1000	C24 – F24
В25	=B11/B5*1000	C25 – F25
В26	=B12/B11*100	C26 – F26

Результаты работы сохраните на диске в папке с именем «Работа 9»

Тема « Проведение расчетов и поиска информации в электронной таблице с использованием формул, функций и запросов »

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

На первом листе рабочей книги создайте таблицу.

Введите формулы в указанные ячейки, установите формат этих ячеек Числовой с двумя знаками после запятой, затем скопируйте формулы:

Ячейки	Формулы	Область копирования
С5	=B5/$B$3*100	C6 – C16
Е5	=D5/$D$3*100	E6 – E16
F3	=D3*10/B3	F5 – F16

Щелкните по пустому серому квадрату, расположенному в левом верхнем углу рабочего листа на пересечении заголовков строк и столбцов таблицы для выделения первого листа и скопируйте содержимое в буфер обмена. Перейдите на второй лист, выделите его и вставьте содержимое буфера. Так же поступите и с третьим листом. Переименуйте листы: Лист 1 назовите Общие данные, Лист 2 – Сбор, Лист 3 – Урожайность.

Перейдите на лист Сбор, измените заголовок таблицы на «Основные производители зерновых». Произведем фильтрацию данных таблицы. Щелкните в любой ячейке шапки таблицы, выберите в меню Данные команду Фильтр – Автофильтр. Щелкните на стрелочке в правом нижнем углу ячейки D2 (Валовой сбор, млн.т), в открывшемся меню выберите опцию (Условие…). Задайте условие, по которому Валовой сбор должен быть больше или равен 7. Щелкните ОК в нижней части окна. Произведите сортировку Валового сбора по убыванию (меню Данные, команда Сортировка…). Теперь таблица листа Сбор выглядит так:

Перейдите на лист Урожайность, измените заголовок таблицы на «Районы с наибольшей урожайностью зерновых». Выполните команду Фильтр – Автофильтр в меню Данные. Щелкните на стрелке ячейки F2 (Урожайность, ц/га), задайте условие фильтрации: больше 17 (цифру введите с клавиатуры). Затем выполните сортировку урожайности по убыванию. Таблица листа Урожайность примет следующий вид:

Результаты работы сохраните на диске в папке с именем «Работа 10»

Тема « Проведение расчетов с использованием формул, функций. Абсолютная и относительная адресация. »

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

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

Щелкните на ячейке А1 и введите весь заголовок таблицы.

Для оформления заголовка выделите ячейки А1 – F1, затем, не снимая выделения:

1) Примените кнопку Объединить и поместить в центре;

2) в меню Формат – Ячейки… выберите вкладку Выравнивание и установите Переносить по словам, щелкните ОК;

3) на панели инструментов утопите кнопки Полужирный и Курсив;

4) установите размер шрифта 14.

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

То же самое следует сделать для изменения ширины строки. При этом указатель мыши следует подводить в области заголовков строк к нижней границе изменяемой строки. Измените, например, ширину строки 1, чтобы в ней был отображен весь введенный заголовок таблицы (указатель мыши следует подводить на границе заголовков строк 1 и 2).

Теперь создадим «шапку» таблицы. Выделите ячейки А2 – F3, установите шрифт Полужирный, выравнивание текста По центру, Цвет фона – фиолетовый, Цвет шрифта – белый. Выделите ячейки А2 – А3, примените кнопку Объединить и поместить в центре, проделайте то же с блоками В2 – В3 и F2 – F3, а также С2 – Е2. Введите текст «шапки» таблицы.

Выделите ячейки А4 – А13, установите выравнивание текста по центру, в меню Формат – Ячейки… выберите вкладку Выравнивание – Вертикальное – По верхнему краю.

Для ячеек В4 – В13 установите шрифт Курсив, формат ячеек – перенос по словам, выравнивание – по верхнему краю.

Выделите диапазон ячеек А13 – F13, прочертите линии рамки, используя соответствующую кнопку панели инструментов.

Заполните таблицу данными. В ячейки таблицы, помеченные знаком «?», введите формулы, приведенные ниже. Скопируйте формулы из перечисленных ячеек столбца С в смежные ячейки столбцов D, E, F там, где это необходимо. Например, чтобы скопировать формулу из ячейки С6 в блок D6 – E6 следует щелкнуть на этой ячейке, подвести указатель мыши в правый нижний угол выделенной области до появления черного плюса (+), нажать левую кнопку мыши и выделить блок D6 – E6, только после этого отпустить кнопку мыши. В области копирования появятся формулы с измененными адресами ячеек, поскольку в копируемой формуле были использованы относительные адреса. Для того, чтобы ввести в ячейку С9 формулу с абсолютным адресом можно воспользоваться способом ввода формул с клавиатуры, или ввести формулу с относительными адресами ячеек с помощью мыши, а затем добавить знаки «$» до и после наименования столбца F. При копировании этой формулы всегда будет использовано значение ячейки F8.

Ячейки	Формулы	Область копирования
C6	=C4*C5	D6 – E6
F8	=F7/F6	-
C9	=C6*$F$8	D9 – F9
C11	=C9+C10	D11 – F11
C13	=C11/C12	D13 – F13

Щелкните правой кнопкой мыши на ярлычке Лист1, выберите команду Переименовать (можно просто произвести двойной щелчок по названию листа) и наберите новое имя листа – Овощи.

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

В ячейку А1 введите заголовок. Для диапазона А2 – G2 установите перенос по словам. Заполните таблицу данными, не оформляя ее пока.

В ячейку D3 введите формулу =В3*С3. Затем скопируйте ее в блок D4 – D6.

В ячейку D7 нужно вставить функцию суммирования. Для этого выделите ячейку и щелкните на кнопке Автосумма на панели инструментов, нажмите Enter. В ячейке D7 должна появиться запись =СУММ(D3:D6).

В оставшиеся ячейки введите формулы:

Ячейки	Формулы
Е3	=$F$7/$D$7
F3	=E3*D3
G3	=E3*C3

Скопируйте эти формулы в ячейки E4 – G6. Для этого выделите ячейки E3 – G3, подведите курсор в правый нижний угол выделенного блока до появления черного крестика (+), нажмите левую кнопку мыши и, удерживая ее, перетащите крестик вниз до нижнего правого угла ячейки G6.

Теперь оформим таблицу. Для написания заголовка применим формат заголовка первой таблицы. Щелкните на ярлычке листа Овощи выделите ячейки A1 – F1 и нажмите кнопку Формат по образцу на панели инструментов, затем перейдите на лист Травы и выделите блок A1 – G1.

Для форматирования таблицы применим автоформат. Выделите ячейки A2 – G7, в меню Формат выберите команду Автоформат, в окне Список форматов выделите строку Список 3 и щелкните на кнопке ОК. Выделенный блок оформлен в формате Список 3

Результаты работы сохраните на диске в папке с именем «Работа 11»

Тема « Работа с графическими возможностями электронной таблицы »

Цель: с помощью сводных таблиц составьте отчеты по продаже отдельных видов компьютерной техники.

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

[Цена]=[Стоимость закупки] * 1,2

[Сумма]=[Цена] * [Количество].

Переименуйте первый лист, назвав его Мониторы.

Сделайте две копии этого листа, назовите их Ноутбуки и Карманные компьютеры, соответственно.

На листе Ноутбуки измените содержимое столбцов, не содержащих формулы, а также название таблицы так, как показано ниже. Лист Карманные компьютеры такжезаполните соответствующими данными.

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

В ячейку В3 введите связь: =Мониторы!А3

Скопируйте содержимое ячейки В3 в блок С3 – G3.

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

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

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

Теперь можно приступить к работе со сводными таблицами.

Щелкните в любой ячейке таблицы Ассортимент и вызовите команду Сводная таблица. В окне Мастера Вид техники, Фирма, Марка и Количество выберите для названия полей, а Вид техники, Фирма, Марка в название строк. В Конструкторе в Макете отчета сводную таблицу можно представить в сжатой форме, в форме структуры и табличной форме.

По сводным таблицам очень легко построить диаграмму. Щелкните в области таблицы и выберите команду Диаграмма. Переместите ее на отдельный лист.