Работа с блоками ячеек

Дата: 2025-05-25; Просмотры: 1

Оценка:

0.00 из 5.00 — оценок

Скачиваний: 0

Скачать

С блоками ячеек можно выполнять следующие действия:

1)перемещать; 2)копировать; 3)удалять их содержимое.

Упражнение 2.

1. Выделите блок ячеек С1:Н10. Это можно выполнить либо с помощью мыши (удобно использовать, если блоки небольшие), либо путем выделения начального адреса (в нашем случае С1), а затем конечного адреса (в нашем случае Н10) при нажатой клавише Shift .

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

3. Выполните пункт 2, но при нажатой клавише Ctrl. Блок будет копироваться. Верните блок на прежнее место.

4. Для того, чтобы очистить блок ячеек надо выделить его любым способом и нажать клавишу Del .

Примеры.

Задача1. Постройте график функции при а=35,12 и b=145,24. Значения аргумента функции изменяются в пределах от –10 до +10 с шагом h=0,5.

Решение задачи будем выполнять на втором листе Вашего файла. До настоящего момента он имел имя Книга1. Присвойте своему файлу имя Лаб1, используя команду Сохранить как.

Технология решения задачи

Определите формат ячеек А1 и А2 как числовой с двумя знаками после запятой, и введите в ячейку А1 значение 35,12, а в ячейку А2 значение 145,24.

В ячейку В1 введите имя аргумента (букву Х) как текстовую величину. Задайте в ячейке В1 жирный шрифт и выравнивание по центру.

В ячейку С1 введите наименование функции как текстовую величину . Увеличьте ширину ячейки С1 на необходимую величину и задайте в ней жирный шрифт и выравнивание по центру.

Определите формат ячеек В2:В42 как числовой с тремя знаками после запятой. Введите в ячейку В2 начальное значение аргумента –10, а в ячейку В3 следующее значение аргумента, равное числу –9,5. Выделите ячейки В2 и В3, а затем с помощью черного крестика заполните значения аргумента до конечного (строка 42).

Скопируйте формат ячеек с помощью кнопок Копировать и Формат по образцу. При этом мышь примет вид кисточки. Щелкните мышью по ячейке С2. В ячейку С2 введите формулу:

=$a$1*sin($a$2*b2)+$a$2*cos($a$1*b2)

Затем с помощь черного крестика скопируйте формулу в ячейки С3:С42. Таким образом, Вы получили искомые значения заданной функции.

Выделите значения функции (ячейки С2:С42) и на их основе постройте график функции. Для этого щелкните по кнопке Мастер диаграмм на панели Стандартная, затем следуйте указаниям мастера (тип – график, вид – 1, ряды данных в столбцах). В результате получите график вида:

Улучшите вид графика. Для этого добавьте название графика, названия осей и определите легенду. Легенда располагается над рядом значений функции, если ряды данных в столбцах и справа от ряда значений функции, если ряды данных в строках. В данном случае легенда расположена в ячейке С1. Чтобы увидеть легенду на графике, ее надо выделить вместе со значениями функции (С1:С42). Улучшенный график поместите в блок ячеек с начальным адресом D20. Вид его должен быть следующий:

Проведите исследование функции. Для этого измените значения констант а и b: a=500, b=1000. Убедитесь, что при этом выполняется мгновенный пересчет значений функции.

Сохраните файл, используя кнопку на стандартной панели инструментов Сохранить.

Задача 2. Используя выше рассмотренную технологию постройте график функции для трех значений параметров:

1. a=100, b=200

2. a=150, b=300

3. a=200, b=400.

Аргумент Х изменяется в диапазоне от –5 до 2 с шагом 0,5. При этом график должен иметь вид:

Задание необходимо выполнить на третьем листе Вашего файла Лаб1. Сохраните файл, используя кнопку на стандартной панели инструментов Сохранить.

Задание 2.

Учет результатов экзаменационной сессии ведется с использованием электронных ведомостей. Типовые ведомости создаются для групп и содержат списки студентов (фамилия, имя, отчество, № зачетной книжки). При вводе учетных данных выполняется проверка полноты заполнения ведомости, правильности ввода оценок 2,3,4,5, н/я (неявка), вычисляется средний балл по дисциплине.

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

· если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;

· если средний балл от 3 до 4,5 (включительно), выплачивается минимальная стипендия;

· если средний балл меньше 3, стипендия не выплачивается.

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

Группа №

ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ

Группа№ ________ Дисциплина_____________________

отлично «хорошо»

«удовлетворительно»

«неудовлетворительно»

«неявки»

ИТОГО

Рис. 1. Форма экзаменационной ведомости

ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ Группа №

Минимальный размер стипендии

Итого стипендиальный фонд ___________

Рис. 2. Форма стипендиальной ведомости

Технология работы

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

· установите указатель мыши в ячейку, куда будете вводить текст, и щелкните левой кнопкой, появится рамка;

· введите текст и нажмите клавишу ввода <Enter>;

· переместите указатель мыши в следующую ячейку и щелкните левой кнопкой;

· введите текст, нажмите клавишу ввода <Enter> и т.д.

Таблица 1. Содержимое ячеек

2. Отформатируйте ячейки А1:Е1:

· выделите блок ячеек и введите команду Формат ячеек;

· на вкладке Выравнивание выберите опции:

Ø Горизонтальное — центрировать по выделению,

Ø Вертикальное — по верхнему краю.

· выделите текст жирным шрифтом, нажав на панели инструментов кнопку [ж];

3. Отформатируйте шапку таблицы, применяя к каждой ячейке следующую технологию:

· установите указатель мыши в ячейку, например В5:

· вызовите контекстное меню и выберите команду Формат ячеек;

· на вкладке Выравнивание задайте параметры:

Ø Горизонтальное: обычное.

Ø Вертикальное: по верхнему краю.

Ø Переносить по словам: поставить флажок.

Ø Ориентация: горизонтальный текст (по умолчанию)

Ø Нажать клавишу <ОК>.

Отформатируйте по ширине столбцы А, В, С, D5, E5.

4. Заполните ячейки столбцов В и С данными о студентах учебной группы приблизительно 5-6 строк. Отформатируйте данные.

5.Присвойте каждому студенту порядковый номер:

· введите в ячейку А6 число 1;

· установите курсор в нижний правый угол ячейки А6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер;

· выполните команду локального меню Заполнить ряды.

6. Сохраните рабочую книгу по типу шаблон:

· выполните команду ФАЙЛ, Сохранить как;

· в диалоговом окне установите следующие параметры:

Ø Диски: имя вашего диска.

Ø Каталоги: имя вашего каталога.

Ø Имя файла: SESSION.XLT.

Ø Тип файла: шаблон.

Задание

В созданном шаблоне таблицы SESSION.XLT рассчитайте:

· количество оценок определенного вида, полученных в данной группе;

· скопируйте несколько раз (по числу экзаменов в сессию) этот шаблон на другие листы и проведите коррекцию оценок по каждому предмету;

· на новом листе создайте ведомость стипендии (см. рис.2), куда скопируйте список группы из экзаменационной ведомости;

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

·

Рисунок 3.

Рисунок 4.
Технология работы

1. Для подсчета количества разных оценок в группе необходимо использовать дополнительно для каждого вида оценки столбцы: F (для пятерок), G (для четверок), Н (для троек), 1 (для двоек), J (для неявок) (см. рис. 3). В эти столбцы введите вспомогательные формулы. Логика работы формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего дополнительного столбца как 1. По остальным ячейкам данной строки в дополнительных столбцах устанавливается 0.

Проделайте подготовительную работу, вводя названия дополнительных столбцов в ячейки F5, G5, Н5, 15, J5.

2. Воспользуйтесь «Мастером функций» для задания исходных формул. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:

· установите курсор в ячейку <F6> и выберите мышью на панели инструментов кнопку Мастера функций [f_x].

· в 1-м диалоговом окне выберите вид функции:

Ø Категория: логические.

Ø Имя функции: ЕСЛИ.

Ø Щелкните по клавише <ДАЛЕЕ>.

· во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операнды логической функции: Логическое выражение —D6 = 5 (для ввода адреса ячейки щелкните в ней левой кнопкой мыши).

Ø Значение, если истина - 1.

Ø Значение, если ложно - 0.

· щелкните по кнопке <3акончить>.

3. С помощью Мастера функций введите формулы аналогичным способом в остальные ячейки данной строки.

4. Скопируйте эти формулы во все остальные ячейки дополнительных столбцов:

· выделите блок ячеек F6:J6;

· установите курсор в правый нижний угол выделенного блока и, нажав правую кнопку мыши, протащите ее до конца вашей таблицы.

5. Определите имена блоков ячеек по каждому дополнительному столбцу. Рассмотрим это на примере дополнительного столбца F:

· выделите все значения дополнительного столбца (F6: последняя ссылка);

· введите команду ВСТАВКА, Имя, Определить;

· в диалоговом окне в строке "Имена в рабочей книге" ввести слово ОТЛИЧНО;

· щелкнуть по кнопке <Добавить>;

· проводя аналогичные действия с остальными столбцами, вы создадите еще несколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.

6. Выделите столбцы F - J целиком и сделайте их скрытыми:

· установите курсор на названии столбцов и выделите столбцы F - J;

· введите команду ФОРМАТ, Столбец, Скрыть.

7. Введите названия итогового количества полученных оценок в группе в столбец А согласно рис.3: Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, Итого.

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

· установите указатель мыши в клетку подсчета количества отличных оценок;

· щелкните на кнопке <Мастер функций>;

· в диалоговом окне 1 выбрать:

· категория функции - Математические и тригонометрические; имя функции - СУММ; щелкнуть на кнопке <ШАГ>;

· в диалоговом окне 2 в строке ЧИСЛО 1 установить курсор и ввести команду ВСТАВКА, Имя, Вставить;

· в диалоговом окне выделить имя блока ячеек "Отлично", щелкнуть на кнопке <ОК>;

· повторить аналогичные действия для подсчета количества других оценок.

9. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом:

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

· щелкните на кнопке < å >;

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

10. Переименуйте текущий лист:

· установите курсор на имени текущего листа и вызовите контекстное меню;

· выберите параметр Переименовать и введите новое имя, например Экзамен 1.

11. Скопируйте несколько раз текущий лист Экзамен 1:

· установите курсор на имени текущего листа и вызовите контекстное меню;

· выберите параметр Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец, нажмите <ОК>. Обратите внимание на автоматическое наименование ярлыков новых листов.

12. Выполните команду СЕРВИС, Параметры, вкладка Вид и установите флажок Формулы. Проанализируйте формулы, а затем повторите указанные действия, сняв флажок Формулы.

13. Создайте новый лист Стипендия, на который из столбцов А и В листа Экзамен 1 скопируйте фамилии и порядковые номера студентов. Оформите ведомость назначения на стипендию согласно рис. 4:

· введите название таблицы — СТИПЕНДИЯ;

· укажите размер минимальной стипендии в ячейке D2;

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

14. Введите формулу в ячейку С5 для вычисления среднего балла студента, щелкните на кнопке <Мастер функций> и выберите в диалоговом окне параметры:

· категория функции — статистическая;

· имя функции — СРЗНАЧ;

· щелкните по кнопке <ШАГ>;

· установите курсор в 1-й строке, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой первого студента по первому экзамену;

· установите курсор во 2-й строке, щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой первого студента по второму экзамену;

· установите курсор в 3-й строке, щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой первого студента по второму экзамену;

· щелкните по кнопке <3акончить>;

· в ячейке С5 появится значение, рассчитанное по формуле =СРЗНАЧ('Экзамен'!06;'Экзамен(2)'!06;'Экзамен(3)'!06)

15. Скопируйте формулу по всем ячейкам столбца С.

16. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок по технологии, описанной в п.13 с помощью функции CЧET. Функция СЧЕТ подсчитывает количество числовых значений в списке (В случае необходимости воспользуйтесь справкой).

17. Введите в ячейку Е5 формулу для вычисления размера стипендии студента:

=ЕСЛИ(И(С5>=4,5;D5=3);$D$2*1,5;ЕСЛИ(И(С5>=4;D5=3);$D$2;0))

18. Скопируйте эту формулу в другие ячейки столбца Е.

19. Выполните команду СЕРВИС, Параметры, вкладка Вид и установите флажок Формулы. Проанализируйте формулы в ячейку Е5, а затем повторите указанные действия, сняв флажок Формулы.

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

21. Сохраните книгу с типом шаблон в каталоге XLSTART, укажите имя шаблона — SESSION.XLT.

22. При очередном перезапуске Excel и выполнении команды ФАЙЛ, Создать данный шаблон появляется в списке доступных для выбора. Завершите работу с Excel, повторно запустите его и выполните создание новой рабочей книги по данному шаблону.

Задание 3

Диаграммы

Цель работы: научиться строить диаграммы различных типов.

Далеко не всегда числа в таблице позволяют составить полное впечатление, даже если они рассортированы наиболее удобным для пользователя способом. Графический способ представления информации всегда более информативен. Используя имеющиеся в Microsoft Excel шаблоны диаграмм, вы можете получить наглядную картину данных вашей таблицы, причем, не ограничивая себя в выборе вида диаграммы. Это может быть обычный график, а можно выбрать объемную круговую, цилиндрическую или лепестковую диаграмму.

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

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

В нашем случае данными диаграммы будут сведения, содержащиеся в ячейках A6:A8.

Теперь необходимо определиться с данными для диаграммы. Это могут быть сведения из ячеек B6:B8, C6:C8, D6:D8 и E6:E8. Можно сделать диаграмму и для всех четырех столбцов. Но для первого примера, чтобы не запутаться, возьмем все тот же сентябрь. То есть построим диаграмму «Количество перевезенных пассажиров за сентябрь». В этом случае данными для диаграммы будут сведения, содержащиеся в ячейках B6:B8.

Рис.1

Выделите курсором ячейки A6:B8 и нажмите на кнопку «Мастер диаграмм». Появится диалоговое окно, в левой части которого будет список возможных диаграмм, а в правой – варианты выбранной вами диаграммы (по умолчанию в окне отображается гистограмма).

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

Допустим, вы хотите сделать цилиндрическую диаграмму. Теперь надо выбрать, какой именно вид она будет иметь. Для этого нажмите по очереди на каждый из предложенных образцов и прочитайте описание в правом углу. Можно даже нажать на кнопку «Просмотр результата» и посмотреть, как примерно будет выглядеть ваша собственная диаграмма. Если вас не устраивает ни одна диаграмма из набора «Стандартные», можно нажать кнопку «Нестандартные» и выбрать тип диаграммы из этого списка. Пусть выбрана диаграмма «Цилиндрическая/Гистограмма со столбцами в виде цилиндров». Появится следующее диалоговое окно, отображающее внешний вид будущей диаграммы и способы ее построения:

1. Ряды в строках;

2. Ряды в столбцах.

Выберем «Ряды в строках» и нажмем кнопку «Далее».

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

Например:

· в меню «Подписи данных» можно пометить пункт «значение», тогда над столбцами (цилиндрами) диаграммы появятся числовые значения, присущие каждому региону;

· если в меню «Таблица данных» пометить пункт «Таблица данных», то внизу под диаграммой появится таблица с числовыми значениями для каждого региона;

· можно убрать или добавить основные и промежуточные линии сетки на различных осях координат (в меню «Линии сетки»);

· можно убрать или добавить сами оси координат (в меню «Оси»);

· можно убрать или добавить легенду, которая по умолчанию располагается справа от диаграммы, а также изменить ее местоположение (в меню «Легенда»);

· наконец, для удобства и наглядности можно присвоить название всей диаграмме и отдельным ее частям (меню «Заголовки»).

Допустим, вы хотите, чтобы легенда диаграммы располагалась внизу – отметьте соответствующий пункт в меню «Легенда», а чтобы над каждым столбцом стояло его числовое значение – отметьте пункт «значение» в меню «Подписи данных». Можете поместить в диаграмму саму таблицу, если пометите галочкой пункт «Таблица данных».

Меню «Заголовки»:

· в строку «Название диаграммы» впишите «Количество перевезенных пассажиров за сентябрь»;

· в строку «Ось X (категорий)» впишите «регион»;

· в строку «Ось Z (значений)» впишите «пассажиры».

После этого еще раз нажмите кнопку «Далее».

Появится последнее диалоговое окно, в котором программа предложит вам выбрать место размещения диаграммы:

· на отдельном листе (листе диаграммы);

· или на имеющемся (на текущем листе книги).

Если вы вспомните, что забыли на каком-либо этапе создания диаграммы отметить какие-то параметры, или решите изменить сам тип диаграммы, нажмите кнопку «Назад». Вы сможете вернуться на любое количество шагов и внести любые изменения (разумеется, до того как вы нажмете кнопку «Готово», хотя и после этого вы сможете отредактировать диаграмму, повторно вызвав «Мастера диаграмм» или пункты «Тип диаграммы», «параметры диаграммы» и пр. Контекстового меню области диаграммы).

Отметьте пункт «имеющемся» и нажмете кнопку «Готово». На рабочем поле текущего листа появится диаграмма, подобная той, что изображена на рис.2.

Рис.2

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

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

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

Точно таким же образом можно изменить ширину диаграммы.

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