Домашняя работа

Табличный процессор MS Excel

Файл Excel называется рабочей книгой. Рабочая книга состоит из совокупности листов. Каждый лист представляет собой таблицу, разбитую на ячейки. Ранее такие файлы носили название "электронные таблицы" (так как лист в них был всего один). А программа для работы с ними носит название "процессор электронных таблиц" (processor — обработчик). Приложение Ехсеl и является таким обработчиком.

В нижней части рабочего поля имеются ярлычки листов. На них размещены имена: Лист1, Лист2... Ярлычок активного листа выделен (он белого цвета, имя листа — полужирным шрифтом). Перемещайтесь с одного листа на другой, щелкая по ярлычкам мышью.

Рабочий лист разбит на ячейки (клетки). Ячейки расположены на пересечении строк и столбцов. Слева и сверху расположены адресные полосы. На левой адресной полосе нанесены номера строк: 1.2,.... На верхней адресной полосе — адреса столбцов: А, В,....Z, АА, АВ,... (Иногда говорят о заголовках строк и столбцов.)

Адрес ячейки составлен из имени столбца и номера строки (как в шахматах). Верхняя левая ячейка имеет адрес А1. Ячейка D3 расположена на пересечении столбца D и третьей строки. Сделайте текущей ячейку D3 с помощью мыши (щелкните мышью в ячейке D3). Обратите внимание, что адрес D3 появился в окне, над столбцом А. Это окно называется полем имени. Переместитесь в АС45 с помощью клавиш управления курсором или мыши. Вернитесь в ячейку А1. Переместитесь в АС45 с помощью команды меню ПравкаПерейти. В диалоговом окне (в поле Ссылка) набираете адрес ячейки АС45 (можно набрать ас45).

Способы перемещения по рабочему листу. Перемещение по соседним ячейкам при помощи клавиш:

<Tab> — слева направо, <Shift+Tab> — справа налево,

<Enter> — сверху вниз, <Shift+Enter> — снизу вверх.

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

Для выделения с помощью мыши:

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

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

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

нескольких строк — не отпуская кнопку после щелчка, протащить мышь по адресной полосе строк по соответствующим цифрам;

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

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

Блоки и их выделение. Блок — прямоугольная область смежных ячеек. Адрес блока — адреса левой верхней и правой нижней ячеек блока, разделенные двоеточием. Например, блок А2:С4 —область, для которой адрес верхнего левого угла А2, а правого нижнего угла С4.

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

Для выделения блока с помощью клавиатуры: выделить левую верхнюю ячейку, нажать клавишу <Shift> и, удерживая ее, перемещать курсор стрелками до правой нижней ячейки.

Для выделения совокупности блоков: выделить первый блок, нажать клавишу <Ctrl> и, не отпуская ее, выделить последующие блоки, отпустить клавишу <Ctrl>.

Домашняя самостоятельная работа (Основные операции в Excel)

Упражнение 1 Приемы выделения больших блоков

Нужно выделить А1 :С200.

1 -й способ:

1)щелкнуть мышью на ячейке А1;

2)воспользоваться правой полосой прокрутки, чтобы сделать видимой строку 200;

3)прижать клавишу <Shift> и щелкнуть С200.

2-й способ:

1)щелкнуть мышью на ячейке А1;

2)выбрать команду на вкладке Главная > Найти и выделить > Перейти или нажать клавиши

CTRL+G;

3)набрать в поле ввода диалогового окна Переход адрес С200;

4)прижать клавишу <Shift>

5)щелкнуть ОК.

Выделите В12:ВМ430. Снимите выделение.

Упражнение 2 Содержимое ячеек, ввод и редактирование. В ячейку можно ввести текст, число или формулу (дата и время, как мы позже увидим, частный случай числа). Для ввода данных (текстовой строки или числа) необходимо переместиться в нужную ячейку, набрать данные и нажать клавишу <Enter>.

Введите в ячейку А1 слово числа, но пока не нажимайте клавишу <Enter>. Обратите внимание, что в строке ввода слева от поля ввода появились три кнопки:

косой крестик – щелчок по нему означает отмену ввода и эквивалентен нажатию клавиши

<Esc>,

галочка – щелчок по галочке завершает ввод и эквивалентен нажатию клавиши <Enter> или

<Tab>,

Для редактирования (изменения) нужно выделить ячейку и перейти в строку ввода, щелкнув по ней мышью или нажав функциональную клавишу <F2> (редактирование). Редактирование завершается так же, как и ввод. Исправьте содержимое А1: замените строчную букву ч прописной Ч. Выделите А1, нажмите <F2>, нажмите <Home> (перемещение в начало строки), нажмите <Del> и нажмите Ч.

В ячейки А2 и АЗ введите числа 24 и 47. Обратите внимание, что текстовая строка Числа выровнена по левому краю ячейки, а числа 24 и 47 — по правому краю. Введите в ячейку А4 формулу =А2+АЗ. Формула начинается со знака равенства. В ячейке отображается результат вычисления по формуле: 71, а в строке ввода (если выделена ячейка А4) — сама формула. (Если вместо результата вычисления 71 в ячейке отображается #ИМЯ?, проверьте, может быть, Вы ввели формулу русскими буквами).

Исправьте формулу на =А2*2+АЗ (содержимое ячейки А2 умножить на 2 и прибавить содержимое ячейки АЗ). Для этого выделите ячейку А4, нажмите <F2>, внесите нужные исправления нажмите <Enter>.

Введите в ячейку А5 формулу =А4-АЗ, но уже другим способом.

что в ячейках А4 и А5 изменились результаты. У Вас должно получиться следующее (см. рис. 1).

Упражнение 3 Перемещения блоков. В результате выполнения упражнения 2 блок А1:А5 заполнен данными и формулами. Так как этот блок ок-

2

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

1)Переместим блок А1:А5 в новое положение D3:D7. Так как перемещение проводится в пределах экрана, сделаем так: выделим блок А1:А5, поместим курсор на границу блока (он примет вид стрелки!), нажимая и не отпуская левую кнопку мыши, переместим его в новое положение D3:D7. Изучите, как изменились формулы в ячейках D6 и D7.

2)Скопируем блок D3:D7 на второй лист текущей рабочей книги в ячейки В2:В6. Выделим блок D3:D7, поместим его в буфер (команда Копировать вкладка Главная), перейдем на следующий рабочий лист, выделим В2, извлечем из буфера (команда Вставить), нажмем клавишу <Esc>. Обратите внимание, что на втором листе мы выделяем лишь одну ячейку

— в нее будет помещен левый верхний угол копируемого блока.

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

Упражнение 4 Операции с рабочими листами.

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

2)Копирование рабочего листа в пределах рабочей книги. На практике часто приходится дублировать рабочие листы, чтобы вносить в них небольшие изменения, а не проектировать "с нуля". Скопируйте Лист3. Для этого поместите курсор мыши на ярлычок листа, щелкните правую кнопку мыши и в контекстном меню выберите Переместить/скопировать. Появится диалоговое окно Переместить или скопировать. Выберите мышью вариант (переместить в конец), установите флажок Создавать копию (щелкните мышью — появится галочка). В результате появится рабочий лист с именем Лист3(2).

3)Вставка нового рабочего листа. Выберите во вкладке Главная команду Вставить>Лист. В книге появляется рабочий лист перед активным листом. Вставьте новый рабочий лист перед Лист1. (Обратите внимание на то, как он называется!)

4)Переименование листа. Листам можно дать новые имена. Поставьте курсор на ярлычок листа Лист1, нажмите правую кнопку мыши. В контекстном меню выберите пункт Переименовать. Дайте имя: Пример. (В дальнейшем, когда будет говориться о выборе пункта из контекстного меню, слова "нажмите правую кнопку мыши", как правило, будут опускаться.) Можно и проще: сделать двойной щелчок по ярлычку листа и ввести его новое имя.

5)Удаление рабочего листа. Удалите лист с именем Лист3(2): поставьте на его ярлык курсор мыши и в контекстном меню выберите Удалить.

6)Очистка рабочего листа. Уничтожьте содержимое рабочего листа Лист2. Перейдите на него и щелкните правой кнопкой мыши кнопку на пересечении адресных полос. Будут выделены все ячейки рабочего листа. В контекстном меню выберите Удалить. Лист остался, удалено лишь его содержимое.

Упражнение 5 Вставка пустых строк и столбцов. На листе Пример вставьте три пустые строки перед третьей строкой. Для этого выделите на левой адресной полосе строки 3, 4, 5 и во вкладке Главная выберите команду Вставить>Строки на лист или в контекстном меню выберите пункт Вставить. Аналогично вставьте два пустых столбца перед столбцом А.

Упражнение 6 Заполнение блока одинаковыми числами. Перейдите на Лист2. Требу-

ется ввести во все ячейки блока В1:D9 одно и то же число 5. Выделите блок В1:D9. Введите с клавиатуры число 5. Нажмите сочетание клавиш <Ctrl+Enter>. Снимите выделение. Введите в блок В3:Р3 число 10.

Упражнение 7 Прогрессии. Перейдите на Лист4 и назовите его Прогрессии.

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

1)Выделите А2. Выберите во вкладке ГлавнаяЗаполнить Прогрессия. В диалоговом окне введите: Расположение: по столбцам, шаг 1, предельное значение 14. Нажмите кнопку ОК.

3

2)Если перед вызовом меню выделить блок для ввода, например А2:А10, то предельное значение задавать не нужно. Проделайте это (предварительно очистив блок АЗ:А15).

3)Порядковые номера можно ввести и без использования меню. Введем порядковые номера в столбец С, начиная с С2. Для этого введем в С1 число 1, в С2 число 2, выделим блок С1:С2, поставим курсор мыши на маленький черный квадратик в правом нижнем углу ячейки — маркер заполнения , и нажмем на левую клавишу мыши. Не отпуская ее, потащим маркер заполнения вниз (рядом с маркером появится квадратик, в котором меняется текущий порядковый номер по мере продвижения маркера вниз). На ячейке С15 отпускаем левую кнопку мыши. Блок С2:С15 заполнен порядковыми номерами.

4)Заполните блок Е1:Е12 нечетными числами, начиная с 7.

5)Введите в строку, начиная с С4, арифметическую прогрессию с первым элементом 4, разностью -1, предельным значением -5.

6)Введите в столбец, начиная с Р6, геометрическую прогрессию с первым элементом 4, знаменателем 2, последним элементом 256. А что получится, если в качестве последнего элемента задать 254?

Упражнение 8 Автозаполнение. С помощью маркера заполнения легко "размножить" строки, заканчивающиеся порядковыми номерами.

1)Введите в ячейку K6 текст "Товар 1". Поставьте курсор мыши на маркер заполнения этой ячейки и тащите его вниз до ячейки K10. Вы получите в ячейках строки "Товар2", "Товар3" и т.д.

2)Введите в ячейку L6 текст "1 место". Поставьте курсор мыши на маркер заполнения этой ячейки и тащите его вправо до ячейки N6. Вы получите в ячейках строки "2 место", "3 место".

3)Проверьте как работает автозаполнение с текстом без чисел, с днями недели, названиями месяцев.

Упражнение9 Форматирование. Вставьте в рабочую книгу новый лист и назовите его Форматирование. Основные команды по форматированию ячеек расположены во вкладке Главная.

Рассмотрим некоторые из этих команд.

1)Введите в ячейки А1, В1, С1 строки Начислено, Удержано, Долг. Текст в ячейке А1 частично обрезан. Для изменения ширины столбца А необходимо поместить курсор между названиями столбцов А и В (курсор примет вид двунаправленной

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

Другой способ изменения ширины столбца – выполнить автоподбор ширины столбца. Для этого выделите столбцы А:С и выберите Главная>Формат>Автоподбор ширины. Ширина

столбцов будет соответствовать длине строк.

4)Окружите каждую ячейку текущей области рамкой.

5)Создание заголовка текущей области. Озаглавим текущую область Расчеты. Для этого необходимо вставить строку перед первой и объединить ячейки А1:С1. Чтобы объединить ячейки, их необходимо выделить, затем выбрать соответствующую кнопку на панели инструментов. В результате, вместо трех выделенных ячеек появится одна, текст в которой

4

будет выравниваться по центру. Введите в ячейку А1 слово Расчеты. У Вас должно получиться (см. рис. 2).

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

1)Введите в ячейку А3 число 38,787. Выделите эту ячейку и нажмите мышью кнопку Уменьшить разрядность. Число примет вид 38,79. Однако в строке ввода Вы по-прежнему видите 38,787. Само число не изменилось, изменилось лишь его представление: две цифры после десятичного разделителя. Продолжайте щелкать по этой кнопке и изучайте результат. Щелкая кнопку Увеличить разрядность, добейтесь, чтобы в ячейке вновь отображалось

38,787.

2)Введите в B3 число 45547,678. Нажмите кнопку Денежный формат. Число приобретает вид: 45 547,68 р.

3)Введите в ячейку C3 число ,12. Будет выведено число 0,12. Щелкните по кнопке Процентный формат. Число примет вид 12%. В ячейку А6 введите 0,14. Также наложите процентный формат. Получится 14%.

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

Вставьте в рабочую книгу новый лист и назовите его Вычисления. Введите в ячейку А1 букву х, а в ячейку А2 букву у. Присвоим ячейкам В1 и В2 имена х и у: выделим В1, в окне ввода имени над столбцом А (в одной строке со строкой ввода) появится адрес В1, выделим его мышью и наберем х, нажмем <Enter>. Аналогично дадим ячейке В2 имя у.

LOG(число, основание_логарифма) возвращает логарифм числа по заданному основанию Щелкаем ОК и переходим в диалоговое окно Мастер функций – шаг 2 из 2. В этом окне поля для ввода аргументов функции. В окно Число вводим 2, в окно Основание_логарифма вводим 3. Обратите внимание, что ниже и правее поля ввода появится результат 0,630929754. Можно сейчас нажать ОК, а затем перейти к редактированию формулы, чтобы добавить +1, или, находясь во втором окне мастера функций щелкнуть мышью в поле ввода и сразу вслед за сформированной частью формулы =LOG(2;3) ввести +1. Окончательный вид формулы =LOG(2;3)+1. Нажимаем клавишу <Enter>. Результат: 1,63093.

5

функции Excel: =TAN(x)+1 и =КОРЕНЬ(z), их композиция: =КОРЕНЬ(TAN(x)+1), результат: 1,468952. Для вставки функции TAN в качестве аргумента функции КОРЕНЬ, используйте панель функций, которая открывается под окном имени ячейки слева от строки ввода.

Упражнение 12 (Ссылки)

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

Например, запишите в ячейку В2 формулу =А1*2. А теперь скопируйте эту формулу из ячейки B2 в ячейку B3. Посмотрите как формула изменилась, при копировании относительная ссылка А1 автоматически изменилась на A2.

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

Например, запишите в ячейку С2 формулу =$А$1+В2. А теперь скопируйте эту формулу из ячейки С2 в ячейку С3. Посмотрите как изменилась формула. При копировании ссылка $А$1 остается прежней.

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

Например, запишите в ячейку D2 формулу =А$1+В2. При копировании этой формулы в ячейку Е3, смешанная ссылка в формуле изменится с A$1 на B$1.

ПРИМЕЧАНИЕ Используйте функциональную клавишу F4 для изменения типа ссылки (абсолютная/относительная/смешанная)

6

Задание 2. Выбор решения. Функция ВПР

Рассмотрим задачу «Переход от шкалы отношений к порядковой шкале»

Относительная шкала (ratio scale) обладает всеми свойствами номинальной, порядковой и интервальной шкал и, кроме того, имеет точку начала отсчета. Таким образом, с помощью относительных шкал мы можем определять и классифицировать объекты, ранжировать их, сравнивать интервалы и разности значений. К тому же имеет смысл расчет коэффициентов (отношений) значений шкал. Поэтому исследователь может сказать, что не только разность значений 5 и 2 равняется разности между 17 и 14, но и то, что 14 больше, чем 2, в семь раз. Общеизвестные примеры относительной шкалы: рост, вес, возраст и деньги. В маркетинге с помощью относительной шкалы измеряются объемы продаж, затраты, доля рынка и количество покупателей.

Порядковая шкала (ordinal scale) — это ранговая шкала, в которой числа присваиваются объектам для обозначения относительной степени, в которой определенные характеристики присущи тому или иному объекту. Она позволяет узнать, в какой мере выражена конкретная характеристика данного объекта, но не дает представления о степени ее выраженности. Таким образом, порядковая шкала отображает относительную позицию, но не значительность разницы между объектами. Примерами порядковых шкал служат качественные ранги, ранги команд в турнирах, социальноэкономические классы и профессиональный статус. В маркетинговых исследованиях порядковые шкалы используются для измерения отношения, мнения, восприятия и предпочтения. Измерительные инструменты подобного типа включают такие суждения респондентов, как «более чем» или «менее чем».

Пусть имеется экспериментальная группа, состоящая из 25 человек (N = 25), и контрольная группа, состоящая из 30 человек (M = 30), и измерение заключается в определении числа хондроцитов в 1 мкм3 ткани регенерата на седьмые сутки после травмы. Это число принимает в реальной ситуации натуральные значения от 0 до 20, то есть, измерения производятся в шкале отношений. Поскольку по результатам работы число хондроцитов оказалось критериальным, то в дальнейшем оно будет обозначено как степень регенерации суставного хряща (СРСХ).

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

Наша задача - перевести результаты эксперимента, например, в трехбалльную порядковую шкалу (шкалу рангов) со следующими уровнями СРСХ (L = 3): низкий (СРСХ меньше либо равна 10), средний (СРСХ строго больше 10, но меньше либо равна 15) и высокий (СРСХ строго больше 15). Рассмотрим два варианта решения этой задачи:

Откройте файл «Результаты измерения СРСХ. xls», содержащий таблицу с результатами измерений СРСХ в контрольной и экспериментальной группах до и после эксперимента. Сделайте копию листа «СРСХ». Переименуйте листы «СРСХ Вариант1» и «СРСХ Вариант2»

Вариант 1

Активизируйте первый лист.

Постройте таблицу с тремя уровнями СРСХ (L = 3):

Таблица переходов

На основании данных первой таблицы для каждого ее столбца вычислите распределение членов экспериментальной и контрольной групп по уровням СРСХ, т.е. подсчитай-

8

те число ее членов, принадлежащих тому или иному диапазону. Например, 9 членов контрольной группы до начала эксперимента характеризовались низким уровнем СРСХ. Результаты занесите в новую таблицу.

Для вычисления новых данных используйте функцию:

СЧЁТЕСЛИ(интервал;критерий)

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

Критерий — критерий в форме числа, выражения, текста или ссылки на ячейку, который определяет, какие ячейки нужно подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки", B4 или "<="&B4.

Итоговая таблица

Отметим, что при подобном переходе от шкалы отношений к порядковой шкале часть информации теряется - в рассматриваемом примере одному и тому же уровню СРСХ соответствуют несколько различных оценок. Следовательно, труднее становится устанавливать совпадения и различия характеристик исследуемых объектов. Поэтому, рекомендуется поставить в соответствие значению по шкале отношений значение по шкале порядков.

9

Вариант 2

Активизируйте второй лист.

Постройте таблицу с тремя уровнями СРСХ (L = 3):

Таблица переходов

Добавьте по 2 столбца в каждую таблицу и исправьте заголовки как показано ниже:

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

ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

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

Искомое_значение — значение, которое должно быть найдено в первом столбце табличного массива. Этот аргумент может быть значением или ссылкой.

Таблица — два или более столбцов данных. Можно использовать ссылку на диапазон или имя диапазона. Значения в первом столбце аргумента «таблица» — это значения, в которых выполняется поиск аргумента «искомое_значение». Эти значения могут быть текстовыми, числовыми или логическими.

Номер_столбца — номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таб-

10