Редактирование содержимого таблицы

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

2. Выполните команду на панели Главная -Копировать для копирования или Вырезать для переноса.

3. Активизируйте ячейку – позицию вставки фрагмента (левый верхний угол).

4. Выполните команду Вставить.

 

Практическое задание 1:  Ввод, редактирование и форматирование данных

Этапы выполнения:

1.Группа специалистов получила заказ на издание монографии. Распределение работы и вознаграждение для каждого автора приведены в таблице. Используя МS Excel, создайте таблицу, введите исходные данные, выполните необходимые расчеты. (В первой таблице показано как необходимо делать, а во второй таблице показано, что должно получиться).

2.Выполните форматирование в соответствии с образцом, приведенном во второй таблице.

3.Сохраните табличный документ в файле Лаб1.

Практическое задание 2. Копирование и перенос фрагментов.

Этапы выполнения:

1.Создайте файл.

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

3.В максимально возможной степени используйте копирование уже существующих фрагментов из файла Лаб1.

4.Выполните форматирование данных в соответствии с образцом.

5.Сохраните табличный документ под именем Лаб2.

 

Вопросы и задания для контроля знаний. 1.Какие возможности предоставляет пользователю MS Excel? 2. Как можно добавить или удалить лист книги? 3. Из каких частей состоит лист? 4. Какие обозначения существуют для частей листа? 5. С чего начинается ввод формул? 6. Как центрировать заголовок таблицы? 7. Где можно выбрать функцию для расчета суммы? 8. Как переместить фрагмент таблицы? 9. Как скопировать фрагмент таблицы?

Тема 19 «Работа с Excel. Ввод формул для выполнения расчетов в таблицах и построение диаграмм. Оформление таблиц»

Цель - формирование практических умений и навыков использования технологии обработки табличной информации в табличном процессоре Microsoft Excel.

План

1 Ввод формул для выполнения расчетов в таблицах Microsoft Excel.

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

Оформление таблиц.

Материальное обеспечение: компьютерный класс № 412 с набором оборудования для проведения лабораторных занятий.

Теоретический материал

1 Ввод формул для выполнения расчетов в таблицах Microsoft Excel

Формула – это выражение для выполнения расчетов, содержащее числа, знаки арифметических действий, ссылки на адреса ячеек, функции.

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

Блок ячеек – это прямоугольник, в котором указываются адреса ячеек левого верхнего и нижнего правого углов, разделенных двоеточием, например А1:С5. Если в выполняемом действии указан блок ячеек, то задействованы все его ячейки. 

Рабочий лист это созданная таблица для решения задачи, диаграмма, макрос, рисунок. Стандартное имя листа – Лист1, Лист2, …. С рабочими листами можно выполнять следующие действия: 

§ переименование;

§ удаление;

§ вставка;

§ перемещение;

§ копирование.

Эти действия выполняются с помощью контекстного меню при установленном указателе мыши на ярлычке листов или в группе Ячейки вкладки Главная.

Рисунок – создается с помощью группы Иллюстрации вкладки Вставка в самой среде Excel или может быть вставлен из другого графического редактора.

Типы данных:

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

2. Число. В ячейке Excel можно отобразить три типа числовых данных (констант):

a) целые числа – это последовательность цифр от 0 до 9 со знаком или без него: +25; –100.

b) вещественные числа с фиксированной запятой – это десятичные дроби, в которых целая часть отделяется от дробной запятой: 28,25; –3,765.

c) вещественные числа с плавающей запятой – это числа, записанные в следующей форме: 1,5Е+03 или 2Е-08. 

Такую запись еще называют экспоненциальной формой записи  числа.

По умолчанию правильно введенное число выравнивается по правому

краю ячейки. Неправильно введенное число считается текстом и выравнивается по левому краю. Если число не поместилось по ширине ячейки, то вся ячейка заполняется символом #.

3. Формула - это выражение, состоящее из чисел, адресов ячеек,

функций и знаков операций и начинающееся со знака =. Например: 

=(C6+30)*A1-12

 =А1*20%+12*A1*D12.

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

4. Функция – это запрограммированные формулы, позволяющие

производить часто встречающиеся последовательности вычислений (стандартные функции разного назначения).

5. Дата– это дата, представленная в различных форматах, с которой можно производить арифметические и логические операции. Например, 21.10.2011; 21 октября 2011 г. и др.

6. Ссылки на ячейки и диапазоны ячеек.

Адресация ячеек

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

Абсолютная адресация – это ссылки на ячейки, адреса которых не меняются при копировании содержимого ячеек вдоль строк и вдоль столбцов. Сама ссылка остается неизменной независимо от того, куда перенесена ячейка с формулой, т. е. абсолютная ссылка в формуле всегда ссылается на ячейку, расположенную в определенном месте. Для задания абсолютных ссылок используется знак $. Например: $B$2. 

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо относительный столбец и абсолютную строку. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д.

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

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

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

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

Диаграмма– это графическое отображение данных таблицы. Может храниться на отдельном листе, а может сопровождаться текстом или таблицей.

1.Для того чтобы построить диаграмму в Excel вам необходимы исходные данные. Эти исходные данные должны быть оформлены в виде таблицы. При этом столбцы и строки этой таблицы должны быть подписаны.

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

3. После того, как исходные данные подготовлены и выделены, можно приступать непосредственно к построению диаграммы в Excel. Для этого не снимая выделения с исходных данных, переходим на вкладку «Диаграммы» и находим там блок кнопок под названием «Диаграммы».

С помощью этих кнопок можно построить диаграмму любого типа. Например, вам необходимо построить гистограмму. Для этого просто нажимаем на кнопку «Гистограмма» и выбираем один из предложенных вариантов диаграммы.

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

4. Если внешний вид построенной диаграммы вас не устраивает, то вы можете его изменить. Для этого выделите мышкой диаграмму и перейдите на вкладку «Конструктор» или вкладку «Формат». На этих вкладках доступны инструменты для настройки внешнего вида диаграммы, а также ее цвета и даже типа.

Если вы хотите изменить область, данные в которой должны использоваться для построения диаграммы, то выделите диаграмму мышкой, перейдите на вкладку «Конструктор» и нажмите на кнопку «Выбрать данные»

После этого появится окно «Выбор источника данных». Теперь просто выделите мышкой другую область с данными и нажмите на кнопку «Ok».

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

Оформление таблиц

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

Сначала выделите всю таблицу. Кликнув правой кнопкой по выделенным ячейкам выберите пункт "Формат ячеек" и в появившемся окне закладку "Граница" и закладка "Заливка" - попробуйте выставить какие-нибудь параметры и посмотрите как это скажется на таблице.

Так же на ленте Главная имеется элемент для "быстрого" выделения границ выбранной ячейки.

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

Практическое задание 1: Выполнить расчеты в таблице и построить диаграммы.

1. Заполните таблицу по образцу в соответствии с вариантом задания, используя соответствующее форматирование ячеек:

а) выделите ячейки A1:L1 и выберите команду Объединить и поместить в центре. Введите заголовок таблицы (размер шрифта 9, начертание полужирный, тип шрифта Arial);

б) заголовки столбцов (строка 2) отформатируйте по следующим параметрам: размер шрифта 9, начертание полужирный, тип шрифта Times New Roman, выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам;

в) выделите границы ячеек A1:L13.

2. В диапазон ячеек F3:F12 внесите количество проданного товара (на свое усмотрение).

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

а) общую стоимость представленного товара (E3:E12);

б) суммарную стоимость товара (E13);

в) остаток каждого товара после продажи (G3:G12);

г) общую стоимость остатка каждого наименования товара (H3:H12);

д) суммарную стоимость остатка товара в магазине (H13);

е) процент продаж от первоначального количества товара (установить процентный формат одним из способов:

способ1: вкладка Главная→ группа Ячейки→ команда Формат→ Формат ячеек>→ вкладка Число→ Числовые форматы: Процентный;

способ2: контекстное меню → Формат ячеек>→ вкладка Число→ Числовые форматы: Процентный;

способ3: кнопка Процентный формат в группе Число вкладки Главная;

способ4: вкладка Главная→ группа Стили→ команда Стили ячеек→ Числовой формат→ Процентный);

ж) процент остатка от первоначального количества товара (установить процентный формат);

з) новую цену товара за 1 шт. следующим образом: если % остатка больше % продаж, то цена товара за 1 шт. уменьшается на 20 %, иначе цена остается прежней (K3:K12) (функция ЕСЛИ);

и) новую стоимость остатка каждого наименования товара (L3:L12);

к) общую стоимость остатка товара после изменения цены (L13) (функция СУММ);

л) суммарную стоимость товара, остаток которого меньше 50 % (L14) (функция СУММЕСЛИ);

м) суммарную стоимость товара, остаток которого больше или равен 50% (L15) (функция СУММЕСЛИ);

н) выполните проверку правильности расчетов, сделанных в ячейках L14 и L15: их сумма должна быть равна значению ячейки L13. Сумму поместите в ячейку L16 (функция СУММ).

 

4. Постройте гистограмму Товар Цена за штуку (для диапазонов B2:B12 и D2:D12). Несмежные диапазоны ячеек выделяются при нажатой клавише <Ctrl>: сначала выделяется диапазон B2:B12, затем, с нажатой клавишей <Ctrl>, диапазон D2:D12.

а) Для вставки диаграммы выполните последовательность действий: вкладка Вставка→ область Диаграммы→ Гистограмма. В меню команды Гистограмма выберите вид Гистограмма с группировкой. Диаграмма построена. Для форматирования диаграммы воспользуйтесь контекстной вкладкой Макет режима. Работа с диаграммами, которая появляется только при установленном курсоре на

диаграмме. 

б) Переименуйте диаграмму. Для этого выделите первоначальное название диаграммы и напишите новое: Товар Цена за шт. 

в) Перенесите легенду диаграммы. Для этого в группе Подписи в меню команды Легенда выберите команду Добавить легенду снизу. 

г) Сделайте подписи горизонтальной и вертикальной осей: в меню команды Названия осей выберите необходимые параметры.

д) Проанализируйте построенную диаграмму.

5. Постройте гистограмму Стоимость остатка Новая стоимость остатка (для диапазонов B2:B12; H2:H12; L2:L12) в порядке, описанном выше. Несмежные диапазоны ячеек выделяются при нажатой клавише <Ctrl>.

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

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

5. Постройте гистограмму Количество товара Остаток после продажи (для диапазонов B2:B12; C2:C12; G2:G12). Несмежные диапазоны ячеек выделяются при нажатой клавише <Ctrl>. Поместите подписи данных, используя параметр По центру. Проанализируйте построенную диаграмму.

 6. Постройте круговую диаграмму Новая стоимость остатка (для диапазонов B2:B12; L2:L12): Вставка→ Диаграммы→ Круговая.

Несмежные диапазоны ячеек выделяются при нажатой клавише <Ctrl>. В контекстной вкладке Конструктор в группе Макеты диаграмм выберите вид Макет. Далее, используя меню команды Легенда вкладки Макет, расположите легенду диаграммы снизу. Выделив ряды данных круговой диаграммы, в контекстном меню выберите команду Формат подписей данных> и установите необходимые параметры форматирования. Проанализируйте построенную диаграмму.

7. Постройте лепестковую диаграмму Процент продаж Процент остатка (для диапазонов B2:B12; I2:I12; J2:J12): Вставка→ Диаграммы→ Другие→ Лепестковая→ Лепестковая с маркерами. Лепестковая диаграмма представляет значения каждой категории вдоль отдельной оси, которая начинается в центре диаграммы и заканчивается на внешнем кольце. Лепестковая диаграмма позволяет сравнивать совокупные значения нескольких рядов данных. Выделив ряды данных лепестковой диаграммы для процента продаж (линия синего цвета), в контекстном меню выберите команду Формат ряда данных и в разделе Параметры маркера уменьшите размеры маркера. То же самое проделайте и для ряда данных, соответствующего проценту остатка. Проанализируйте построенную диаграмму.

8. Отформатируйте таблицу по приведенным ниже параметрам.

а. Заголовок таблицы Применение статистических функций сделайте жирным шрифтом, размер шрифта – 12 пт. Для объединения ячеек таблицы и центрирования заголовка необходимо выделить ячейки A1:G2 и нажать на кнопку Объединить и поместить в центре, расположенную в группе Выравнивание вкладки Главная. Затем, не убирая курсора с объединенных ячеек, в контекстном меню выберите команду Формат ячеек и в открывшемся диалоговом окне выберите:Ċвкладка Выравнивание→ область Выравнивание→ по горизонтали – по центру; по вертикали – по центру.

б. Заголовки столбцов таблицы (№ п/п ФИО студента Рост Вес и т. д.) в зависимости от варианта задания) – по центру, полужирным шрифтом, размер шрифта – 10 пт. Возможность отображать текст внутри ячейки таблицы в несколько строк достигается следующим образом: 

a) выделить ячейки A3:G3, формат которых требуется изменить;

b) в контекстном меню выберите команду Формат ячеек и в открывшемся диалоговом окне выберите: вкладка Выравнивание→ область Выравнивание→ по горизонтали – по центру; по вертикали – по центру;

c) в области Отображение установить флажок переносить по словам.

в. К тексту ячеек B15:B22 примените начертание курсив и сделайте перенос по словам.

г. Выделите ячейки A1:G13 таблицы. С помощью кнопки Границы→ Все границы группы Шрифт измените границы таблицы.

д. Символ, соответствующий степени 2 числа, можно вставить с помощью команды Символ группы Символы вкладки Вставка. Другой способ указания символа степени: написать степень числа, выделить его, нажать кнопку группы Шрифт и в появившемся диалоговом окне во вкладке Шрифт в области Видоизменение установить флажок надстрочный. 

Вопросы и задания для контроля знаний. 1.Что такое блок ячеек? Что называют формулой? 3. Какие типы данных можно вводить в ячейки таблицы? 4. Чем отличаются виды адресации в формулах? 5. Как присвоить имя диапазону данных? 6. Что называют диаграммой? 7. Что называют функцией? 8. Назовите этапы построения диаграмм? 9. Как оформить таблицу?

Тема 20 «Работа с MS Excel. Работа с мастером функций»

Цель - формирование практических умений и навыков использования технологии обработки табличной информации для работы со стандартными функциями в табличном процессоре Microsoft Excel.

План

1 Работа с мастером функций в таблицах Microsoft Excel.