Ввод и редактирование данных в ЭТ

Введение

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

 

Основные понятия

ЭТ состоят из строк и столбцов. Столбцы обозначаются буквами латинского алфавита (А, В, С...), расположенными в заголовочной части таблицы. Строки идентифицированы цифрами (1, 2, 3,...), расположен­ными в первой колонке. Место пересечения столбца и строки называетсяячейкой. Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и номера строки, например Al, В12.

ЭТ могут содержать несколько рабочих листов, которые объеди­няются в один файл и носят название рабочей книги.

В ЭТ можно работать как с отдельными ячейками, так и с группа­ми ячеек, которые образуют блок - прямоугольную область таблицы.

Адрес блока - это адрес левой верхней ячейки и адрес правой нижней ячейки. В качестве разделителя служит символ - двоеточие (:) или точка (.). Напри­мер блок А1:В2 (А1.В2) включает в себя ячейки Аl, А2, B1, B2. Любому диапазону можно присвоить имя, соответствующее смыслу размещаемых в нем данных.

 

Типы данных

В ЭТ используются следующие типы данных:

текст - любая последовательность символов;

число - числовая константа;

дата - может быть представлена в различных форматах;

формула - выражение, состоящее из чисел, адресов ячеек, функ­ций; например: =А5/Н8*12;

функции - запрограммированные формулы, позволяющие прово­дить часто встречающиеся последовательности вычислений; например: =СУММ(А1:А4).

Создание и обработка ЭТ

Проектирование ЭТ включает следующие шаги: ввод заголовка ЭТ, ввод названий граф документа, ввод исходных данных, ввод расчет­ных формул, форматирование ЭТ с целью придания ей профессиональ­ного вида, подготовку ЭТ к печати и ее печать.

При необходимости ЭТ могут сопровождаться диаграммами и по­яснительными комментариями.

 

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

Заполнение ячеек текстом

Под текстом в Excel подразумевается любая комбинация символов: букв, цифр, пробелов, знаков пунктуации и т. д. Текст в электронных таблицах (ЭТ) обычно служит для задания заголовков (названия чего-либо, фамилии и т.п.) столбцов и строк, написания примечаний к ячейкам или для указания какой-либо другой поясняющей информации (рис. 2). Ввести набранный текст можно, нажав клавишу Еnter или щелкнув мышью по другой ячейке. Отменить ввод данных можно нажатием клавиши Esc, а исправить ошибку при наборе текста можно клавишей Backpace или подведя текстовой курсор к месту ошибки клавишами стрелок.

 

Рис. 2. Внесение текстовой информации и числовых значений в ЭТ

 

Клавиша Еnter перебираетячейки по столбцам, а клавиша Tab – по строкам выделенного блока.

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

 

Ввод числовых значений

Числовые значения в Ехсеl могут состоять из цифр от 0 до 9 и различных специальных символов: “+”, “-”, “/”, десятичной точки (или запятой), знака процента, круглых скобок, а также знаков различных денежных величин. В программе Ехсеl существует возможность автоматически вводить различные специальные символы, в частности десятичные точки, пробелы, знаки процента и денежные единицы, для этого следует пользоваться инструментами форматирования.

Например, чтобы заполнить в таблице (рис.2) столбец под названием Цена, нужно ввести в диапазон ячеек Е3:Е14 значения, после чего выделить указанный диапазон и выбрать команду Формат/Ячейки. В раскрывшемся диалоговом окне (рис.3) следует задать денежный числовой формат, выбрать денежную единицу (в данном случае гривну) указать требуемое число десятичных знаков (в данном примере – 0) и щелкнуть на кнопке ОК.

 

Рис. 3. Диалоговое окно для определения формата ячеек

 

Редактирование данных

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

 

Удаление содержимого ячеек

Если необходимо удалить данные из ячейки, можно стереть пре­дыдущий текст, просто введя в ячейку новые данные. Можно удалить данные из ячейки, выделив ее и нажав клавишу Delete.

Отмена изменений

Отменяются изменения нажатием кнопки Отменить, а возвраща­ются с помощью кнопки Повторить на панели инструментов.

 

Задание к лабораторной работе №1

1. Запустите программу Microsoft Excel.

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

3. Определите адрес текущей ячейки, сделайте текущей ячейку С10.

4. Выделите блок ячеек А1:В5, выделите одновременно ячейки А1 и В5.

5. Создайте следующую таблицу, содержащую следующую информацию о продажах: название таблицы, фамилия продавца, наименование товара, дата продажи товара и цена товара.

a. Введите текст в ячейки:

Адрес ячейки	Текст в ячейке
А1	Отчет о продажах
В3	Продавец
С3	Товар
D3	Дата продажи
E3	Цена

b. Используя автозаполнение, заполните блок А4:А14 числами от 1 до 11;

c. Заполните текстовой информацией столбцы с заголовками Продавец и Товар.

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

e. Введите значения дат в столбец с заголовком Дата продажи, для этого используйте формат даты ДД.ММ.ГГГГ (воспользуйтесь диалоговым окном Формат ячеек).

6. Внесите изменения в диапазон ячеек Е9:Е13, вместо гривен, выберете денежную единицу рубли, а число десятичных знаков замените на 2.

7. Сохраните рабочую книгу под именем Товар_1.

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

 

Задание к лабораторной работе №2

1. Вызовите приложение Excel:

в клетку А1 - заголовок таблицы ВЕДОМОСТЬ ГРУППЫ

в клетки A3:F3 - заголовки столбцов: A3 - № п/п, ВЗ - Ф.И.О., C3:F3 - названия предметов;

используя автозаполнение, введите в клетки А4:А10 порядковые номера;

в клетки В4:В10 внесите фамилии, а в клетки C4:F10 - оценки.

2. Сохраните рабочую книгу под именем Ведомость_2.

3. Используя функцию МАКС найдите максимальный балл в группе: в клетку А14 внесите текст Максимальный балл, а в клетку G14 - функ­цию MAKC(C4:F10). Здесь C4:F10 - блок клеток из которых выбирается наибольшее значение.

4. Определите самую низкую оценку в группе, значение которой помес­тите в клетку G15, а пояснительный текст в клетку А15.

5. Определите средний балл каждого студента:

выделите ячейку НЗ и внесите заголовок для столбца таблицы - Средний балл;

выделите ячейку Н4 и щелкните по кнопке Мастер функций; в списке Категория выделите строку Статистические, в списке Функция - нужное название (СРЗНАЧ) и нажмите кнопку Далее; перетащите диалоговое окно в вверх или низ экрана; выделите клетку с оценками первого студента (C3:F3) и щелк­ните по кнопке Готово;

6. Скопируйте формулу из клетки Н4 в блок Н5:Н10:

· выделите клетку Н4;

· выполните команду меню Правка-Копировать;

· выделите область вставки;

· выполните команду меню Правка-Вставить. Вместо команд ме­ню Правка-Вставить и Правка-Заменить можно воспользоваться кнопками на панели инструментов.

7. Округлите средний бал каждого студента до одной цифры после запятой:

· выделите ячейку IЗ и внесите заголовок для столбца таблицы - Средний балл_Округление;

· выделите ячейку I4 и щелкните по кнопке Мастер функций; в списке Категория выделите строку Математические, в списке Функция - нужное название (ОКРУГЛ) и нажмите кнопку Далее; перетащите диалоговое окно в вверх или низ экрана; в первой строке диалогового окна поставьте курсор и выделите клетку со средним балом первого студента (Н4), затем перейдите на вторую строку диалогового окна и поставьте число разрядов после запятой (1), и щелк­ните по кнопке Готово;

8. Скопируйте формулу из клетки I4 в блок I5: I10.

9. Сохраните рабочую книгу под именем Ведрмость_3.

10. Измените оценки в таблице. Обратите внимание на изменение сред­него балла.

11. Переместите все файлы Ведомость в соответствующую папку на дискете.

 

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

Применение имен в формулах

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

1) в меню Вставка выбрать команду Имя - Присвоить. Откроется диалоговое окно Присвоить имя, где в окне списка Имя перечислены созданные имена блоков;

2) выделить в этом списке нужное имя;

3) исправить имя, если это необходимо;

4) в поле Формула изменить адреса, добавив или удалив ячейки;

5) щелкнуть на кнопке Добавить; новое имя будет добавлено в список, однако старое имя останется в списке. Чтобы изменить имя бло­ка ячеек, надо добавить в список новое и удалить старое имя.

6) для удаления имени выделить его в списке и щелкнуть на кнопке Удалить;

7) закрыть диалоговое окно.

Аналогичные действия можно выполнить, используя поле ввода Имя на панели инструментов.

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

Другой способ перемещения по рабочему листу - использование клавиши F5:

1) нажать клавишу F5. Откроется диалоговое окно Переход, содер­жащее список всех имен ячеек в рабочей книге;

2) В окне списка Перейти выделить нужное имя и щелкнуть по кнопке ОК.

Применение имен в формулах

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

Например, из формулы:

=СРЗНАЧ(Иванов) или =МАКС(Химия)

можно почерпнуть гораздо больше информации, чем из безликой формулы

=СРЗНАЧ(С4:G4) или =МАКС(C4:C10)

выполняются те же самые вычисления (рис. 4).

 

Рис. 4. Присвоение имен по строкам и столбцам

 

Для вставки в формулу имени ячейки или диапазона используется команда Вставка/Имя/Вставить. Если выбрать данную команду (или нажать клавишу <F3>), раскроется диалоговое окно Вставка имени. Выберите имя в списке и щелкните на кнопке ОК, чтобы вставить его в формулу.

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

Завершение работы

Для окончания работы с Microsoft Excel надо в меню Файл выбрать команду Выход. Если на панели появится диалоговое окно с за­просом о сохранении изменений, щелкнуть в нем по кнопкам ДА или НЕТ.

Задание к лабораторной работе №3

1. Откройте рабочую книгу Ведомость_2. Введите изменения: ФИО и названия предметов, перекопируйте как указано на рис. 4.

2. По таблице, изображенной на рис. 4, строкам нужно присвоить имена Иванов, Петров, Сидоров, Харламов и т.д., а столбцам – Химия, Математика, Биология и т. д., используя команду Вставка/Имя/Создать.

3. Используя имена ячеек, определить какая оценка была у Харламова по математике, Сидорова по русскому языку, Иванова по химии, Кузьмина по физкультуре, Овдотенко по биологии, Краморова по химии.

4. Присвойте имя «Оценки» диапазону ячеек С15:С20, используя команду Вставка/Имя/Присвоить.

5. Используя имена ячеек определите среднее значение оценок для каждого студента, округлив при этом, до одного знака после запятой. Используйте формулу: =ОКРУГЛ(СРЗНАЧ(Иванов);1), а затем скопируйте формулу для остальных студентов, изменяя при этом фамилии. Присвойте имя «Среднее_значение» диапазону ячеек Н4:Н10, используя команду Вставка/Имя/Присвоить.

6. Используя имена ячеек, определите максимальное, минимальное значения по предметам.

7. Используя имена ячеек, определите среднее значение всех оценок. Округлите до второго знака после запятой.

8. Сохраните как Имена_1.

 

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

Копирование данных

Абсолютными адресами

Адреса, которые автоматически изменяются прикопировании их в новые ячейки, называются относительными. Однако в формулах мож­но использовать адреса, которые всегда относятся кодним и тем же ячейкам, независимо от того, где находится формула. Такие адреса на­зываются абсолютными. Знак $ указывает на абсолютный адрес. На­пример, формула в клетке С1 использует относительные адреса клеток А1, В1 и абсолютный адрес клетки А4. При копировании формулы из клетки С1 в блок клеток С2:СЗ, адрес клетки А4 не будет меняться и в клетке С2 получим формулу =(А2+В2)*$А$4, а в клетке СЗ=(АЗ+ВЗ)*$А$4.

Изменять типы адресов можно, нажимая клавишу F4. Можно ус­тановить абсолютный адрес строки и столбца ($А$4), или только стро­ки (А$4), или только столбца (SA4), или вернуться к старым адресам. При перемещении, а не копировании формулы адреса будут указывать на прежние ячейки, независимо от того, являются они абсолютными или относительными.

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

 

Задание к лабораторной работе №4

 

1. Откройте рабочую книгу Имена_1.

2. Внесите изменения в данные:

в клетку В12 внесите заголовок Средний балл по предметам;

в клетку С12 - формулу, вычисляющую среднее значение клеток С4:С10;

скопируйте эту формулу в блок клеток D12:G12; таким образом вы найдете среднюю оценку по каждому предмету. Обратите внимание, как изменились адреса клеток в формулах.

3. Переместите формулы из столбца Н в столбец I. Изменились ли ад­реса ячеек в формулах? Почему? Отмените изменения.

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

в клетку А12 введите текст Минимальный размер стипендии, а в ячейку G12 - значение, соответствующее минимуму;

в клетку IЗ введите заголовок столбца - текст Размер стипендии;

в клетку I4 введите формулу для начисления стипендии первому студенту. Для этого воспользуйтесь функцией =ECJIИ(H4>=4;$G$12;0), то е., если содержимое клетки H4>=4 (а это значение среднего балла первого студента), то в клетку I4 занесется значение клетки G12, в про­тивном случае в клетке I4 появится ноль. Для ввода этой формулы можно воспользоваться Мастером функций;

скопируйте содержимое клетки I4 в блок клеток I5:I10. Так как при копировании формулы адрес клетки G12 не должен меняться, то в формуле для нее используется абсолютный адрес.

5. Начислите студентам, средний балл которых выше 4,5 премию в раз­мере 60% минимальной стипендии. Результаты поместите в столбец J (заголовок - в клетку J3, расчетные формулы - в клетки J4:J10).

6. В столбец K введите формулы, вычисляющие итоговую сумму стипен­дии для каждого студента.

7. Используя имена ячеек, в диапазон ячеек L4:L10 начислите стипендию студентам, используя формулу: =ЕСЛИ(СРЗНАЧ(Иванов)>=4;5000;”Стипендии нет”)

8. В ячейку H12 введите 60%, присвойте имя ячейки «Повышенная_ стипендия».

10. Выполните задание пункта 5, используя имена ячеек. Результат поместите в диапазон ячеек M4:М10. Используйте формулу:

=ЕСЛИ(СРЗНАЧ(Иванов)>=4,5;G$12*Повышенная_стипендия;”Нет стипендии”)

11. В ячейку В22 внесите заголовок «Сред_бал_выб». В ячейку С22 средний бал диапазона ячеек С15:С20, используя имя диапазона ячеек «Оценки», округлите результат до двух цифр после запятой.

12. Скопируйте порядковые номера и Ф.И.О. студентов в блок А18:В25, а в блок клеток С18:С25 итоговую сумму стипендии (использовать специ­альную вставку и опцию вставить ссылку).

13. Сохраните рабочую книгу под именем Начисл_Стип.

 

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

 

Выравнивание данных

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

1) выделить нужный блок ячеек;

2) на панели инструментов щелкнуть по кнопке: По правому краю; По левому краю; По центру.

Добавление рамок Для добавления рамок необходимо:

1) выделить нужный блок;

2) на панели инструментов открыть таблицу рамок, щелкнув мы­шью на стрелке рядом с кнопкой Линии рамки;

3) выбрать нужный вид рамки.

 

Изменение формата чисел

Быстро задать стандартный формат числа в ячейке можно с по­мощью команд меню Формат. Для этого надо:

1) Выделить блок ячеек;

2) Щелкнуть по нужной кнопке или в меню Формат <выбрать ко­манду Ячейки, щелкнуть мышью на ярлыке Число. На этой вкладке на­ходятся параметры форматируемых чисел. В окне списка Числовой формат выбрать строку с нужным типом;

3) В списке Число десятичных знаков можно указать количество знаков после десятичной точки.

 

Задание к лабораторной работе №5

 

1. Откройте рабочую книгу Начисл_Стип.

2. Внесите изменения в таблицу:

удалите из таблицы сведения о первом предмете. Подумайте, куда надо сдвинуть оставшиеся данные, и укажите это в диалоговом окне, от­крывающемся при удалении;

добавьте две фамилии и данные (оценки и расчетные формулы) по этим студентам;

проверьте расчетные формулы и при необходимости исправьте их.

3. Увеличьте размер шрифта в заголовке таблицы. Измените шрифт за­головков столбцов таблицы на жирный, шрифт фамилий - на курсив. Если необходимо, увеличьте высоту строк.

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

5. Для ячеек с размером стипендии, надбавкой и итоговой суммой ус­тановите денежный формат. Для ячеек со значениями среднего балла -числовой формат. В обоих случаях учитывайте по два десятичных знака.

6. Сделайте рамки для таблицы.

7. Сохраните рабочую книгу под именем Начисл_Стип_1.

8. Используя автоформат выберите любой вид для таблицы. Сохраните рабочую книгу под именем Начисл_Стип_2.

9. Переместите все файлы Начисл Стип в соответствующую папку на дискете.

 

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

Задание к лабораторной работе №6

 

1. Откройте рабочую книгу Начисл_Стип_2.

2. Внесите изменения в первый рабочий лист:

измените заголовок таблицы на Итоги сессии 1-го семестра;

измените название первого рабочего листа на Семестр_1.

3. Скопируйте содержимое первого листа на второй. Внесите измене­ния во второй рабочий лист:

измените заголовок таблицы на Итоги сессии 2-го семестра;

введите новые данные в таблицу (названия предметов и оценки) и отформатируйте таблицу.

присвойте второму листу имя Семестр_2.

4. На третьем листе составьте сводную таблицу с заголовком Итоговая таблица успеваемости и со следующими столбцами: №, Фамилии, I семестр, II семестр.

5. Скопируйте фамилии студентов с любого листа в итоговую таблицу. В столбец I семестр скопируйте значения среднего балла каждого студента с первого листа, в столбец II семестр - со второго листа. При копировании используйте команду Вставка - Специальная вставка.

6. Переименуйте третий лист в Итог.

7. Отформатируйте итоговую таблицу любым способом.

8. Сохраните рабочую книгу под именем Ведомость_4.

 

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

 

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

Чтобы создать диаграмму на рабочем листе нужно:

1) вызвать Мастер диаграмм. В открывшемся рабочем окне Мас­тер диаграмм шаг 1 из 4 выбрать тип и вид диаграммы;

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

3) в этом же окне выбрать вкладку Ряды. Здесь можно указать на­звания рядов и подписать горизонтальную ось, указывая адреса блоков в поле ввода Имя и в поле ввода Подписи оси X;

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

5) в последнем окне Мастер диаграмм Шаг 4 из 4 указывается раз­мещение диаграммы: на отдельном листе или на имеющемся.

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

При построении диаграмм переход от одного окна к другому вы­полняется при нажатии кнопки Далее, возврат к предыдущему шагу - с помощью кнопки Назад, а отказ от построения диаграммы - кнопкой Отмена. Строя диаграмму, не обязательно выполнять все шаги. Закон­чить построение диаграммы можно в любой момент, нажав в диалого­вом окне Мастер диаграмм кнопку Готово. На каждом шаге построе­ния диаграммы можно наблюдать за изменением ее вида.

 

 

Изменение диаграмм

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

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

 

Задание к лабораторной работе №7

 

1. Используя материал предыдущих работ, создайте с помощью Micro­soft Excel следующую таблицу со своими данными:

Должность
Профессор
Доцент
Ст. преподаватель
Ассистент

2. Постройте гистограмму, показывающую изменение (по годам) со­става кафедры:

выделите таблицу (без заголовков) и вызовите Мастер Диаграмм;

в окне Мастер Диаграмм шаг 1 из 4 выберите тип Стандартные и вид Гистограмма;

в окне Мастер Диаграмм шаг 2 из 4 проверьте указанный блок и расположение данных для построения диаграммы, названия рядов; под­пишите ось X;

на третьем шаге введите названия диаграммы и осей, поместите таблицу данных под диаграмму, нанесите горизонтальную сетку;

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

3. Сохраните рабочую книгу под именем Преподаватели_1.

4. Оставьте на диаграмме данные только для 1998 - 2008 годов.

5. Измените шрифт в названиях диаграммы и осей.

6. Сохраните рабочую книгу под именем Преподаватели_2.

7. Измените тип диаграммы на круговую или кольцевую, показываю­щую соотношение количества человек по должностям в 2008 году, и за­дайте нужные значения. Выполните подходящие подписи для диаграм­мы.

8. Сохраните книгу под именем Преподаватели_3.

9. Откройте в данной рабочей книге лист 2.

10. Постройте график функции Y на интервале [0, 1] с шагом 0,1.

0, если х<0;

Y= x, если х [0, 1];

1, если х> 1.

Для построения графика необходимо:

a) в ячейку А1 занести текст Х, в ячейку В1 – текст Y(X)

используя автозаполнение, заполните диапазон ячеек А2:А12 значениями Х от 0 до 1 с шагом 0,1 (команда Правка/Заполнить/Прогрессия)

b) в ячейку В2 введите формулу для определения значения функции Y(X): ЕСЛИ(А2<0;0;ЕСЛИ(А2<1;1;A2))

c) скопируйте формулу из ячейки В2 в диапазон ячеек В3:В12

d) постройте график функции; тип диаграммы: Точечная

e) присвойте второму листу имя График

11. Перейдите на третий лист.

12. Постройте два графика в одной системе координат на интервале

[-3, 0] с шагом 0,2.

Y1(x) = 2*sin(x)

Y2(х) = 3*cos(2*x) – sin(x)

Для построения графиков необходимо:

a) в диапазоне ячеек А1:С1 введите заголовок Х,Y1(X), Y2(X)

b) используя автозаполнение, заполните диапазон ячеек А2:А17 значениями Х от -3 до 0 с шагом 0,2

c) в ячейку В2 введите формулу для определения значения функции Y1(X) и скопируйте ее в диапазон ячеек В3:В17

d) в ячейку С2 введите формулу для определения значения функции Y2(X) и скопируйте ее в диапазон ячеек С3:С17

e) постройте график функции; тип диаграммы: Точечная

f) присвойте третьему листу имя Графики

11. Вставьте четвертый лист

12. Постройте график функции Y=1/Х на интервале [-1, 1] с шагом 0,1.

13. Присвойте четвертому листу имя Самостоятельно.

 

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

Сортировка данных

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

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

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

 

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

Ключом сортировки в этом случае является столбец с текущей ячейкой.

Отменить сортировку можно, выполнив команду Отменить Сор­тировка в меню Правка.

Фильтрация списков

С помощью фильтрации можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel по­зволяет быстро и удобно просматривать требуемые данные из списка с помощью автофильтра.

Чтобы использовать автофильтр надо:

1. Выделить область списка для поиска с заголовками полей.

2. Выполнить команду Фильтр - Автофильтр в меню Данные. В заголовках каждого столбца появятся кнопки со стрелками вниз.

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

Критерии фильтра находятся в списке в алфавитном порядке. В дополнение к ним имеется еще пять ключей: Первые 10, Условия, Пус­тые, Непустые, Все. Эти ключи позволяют найти в списке десять запи­сей, ведущих по выбранному показателю; записи, удовлетворяющие ус­ловию, заданному пользователем; записи, не содержащие никакой информации; записи, ячейки выделенного поля которых не пусты.

Чтобы снова вывести на экран полный список, нужно в списке критериев фильтрации выделить строку Все или в меню Данные вы­брать команду Фильтр - Показать все. Для отказа от фильтрации - по­вторить Фильтр - Автофильтр в меню Данные.

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

Использование расширенного фильтра предполагает наличие диапазона критериев (условий). Удобнее всего диапазон критериев располагать над списком (рис. 5). Диапазон критериев (условий) должен состоять по крайне мере из двух строк: в верхней строке указываются имена полей (заголовки), во второй и последующих строках - условия отбора, за исключением вычисляемых условий (рис. 6). Заголовки в диапазоне критериев должны точно совпадать с заголовками столбцов в списке (заголовки в таблицу критериев лучше скопировать из исходной таблицы).

 

Рис. 5. Использование расширенного фильтра

Для задания таблицы поиска надо выделить область списка для поиска с заголовками полей.

Для формирования таблицы критериев необходимо:

1. Скопировать имена полей списка, по которым будет задаваться условие, в ту часть рабочего листа, где будет располагаться таблица критериев. При этом число строк в таблице определяется числом крите­риев поиска. Включение пустых строк в таблицу недопустимо.

2. Задать критерии поиска. Задание критериев поиска в виде кон­стант требует точной копии имен тех столбцов, которые задают условия фильтрации. Если критерии связаны оператором И, то они должны на­ходится в одной строке, если ИЛИ - в разных. Можно создать таблицу критериев, в которой задается вычисляемый критерий, по которому ведется поиск записей. Ввод вычисляемых критериев поиска в таблицу критериев требует выполнения следующих правил:

2.1 Формула должна выводить логические значения Истина или Ложь (рис. 6, 7). После выполнения поиска на экран выводятся только те строки, для которых результатом вычисления формулы будет Истина.

Рис. 6. Использование вычисляемого условия в расширенном фильтре

Рис. 7. Использование вычисляемого условия в расширенном фильтре

 

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

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

После чего выполнить команду Расширенный фильтр, по кото­рой на экран будет выведено диалоговое окно, где надо задать область поиска исходного списка, область критериев, указать выполнять фильт­рацию на исходном месте или перенести данные в другую область и ес­ли для отфильтрованных данных задана область вывода, то указать ее (рис.5).

В условии фильтрации можно использовать возвращаемое форму­лой значение. При задании формул в условиях заголовок блока условия оставляют незаполненным. Используемая в условии формула должна ссылаться либо на заголовок столбца, либо на соответствующее поле первой записи списка. Например, в формуле =С12>CP3HAЧ(SС$12:$С$17) С12 ссылается на соответствующее поле (столбец С) первой записи (стро­ка 12) списка.. При использовании заголовка столбца в формуле условия вместо имени диапазона, в ячейке будет выведено значение #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Задание к лабораторной работе №8

1. Откройте рабочую книгу Преподаватели_3.

2. Упорядочьте таблицу на листе Семестр_1 в алфавитном порядке фамилий:

• Выделите столбцы таблицы кроме первого;

• В меню Данные выберите Сортировка;

• В диалоговом окне Сортировка диапазона в поле Сортиро­вать по выбрать имя столбца с фамилиями, и опцию По возрастанию.

3. Выполните аналогичные действия с таблицей на листе Семестр_2, упорядочив ее по убыванию среднего балла.

4. На листе Итог, используя автофильтр, определите студентов средний балл которых в первом семестре выше 3, но меньше или равен 4.5:

· Выделите таблицу;

· Выполните команду Данные - Фильтр - Автофильтр;

· Щелкните по стрелке в столбце Семестр_1 и выберите строку Условие;

· Вдиалоговом окне в одном поле оператора выберите > и в со­седнем поле введите значение 3, в другом поле оператора выберите знак <= и введите значение 4,5;

· Выберите опцию И (так как должны выполняться оба условия одновременно); щелкните по кнопке ОК;

· Сохраните рабочую книгу под именем Ведомость_5.1;

Верните все записи таблицы, щелкнув по стрелке в столбце Семестр_1 и, выбрав значение Все.

5. Аналогично определите студентов, средний балл которых равен 3 во втором семестре и отличников по двум семестрам, сохраняя рабо­чие книги под именами Ведомость_5.2 и Ведомость_5.3.

6. Выполните задания пунктов 4-5, используя расширенный фильтр. Задайте на рабочем листе Итог таблицы поиска, вывода и кри­териев (рис.5 ). Выполните команду Данные - Фильтр - Расширен­ный фильтр, Каждый раз указывая нужную таблицу критериев и выво­да. Таблица поиска для всех трех случаев одна и та же. Сохраните рабо­чую книгу под именем Ведомость_5.4.

7. Используя логическую операцию ИЛИ, определите студентов, чьи фамилии начинаются либо на букву А, либо на Б, либо на В, (либо на любые другие буквы, если таковых не имеется в вашем списке ст