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

Домашняя самостоятельная работа
(Основные операции в 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 (в строке ввода появится ее адрес). Введите знак минус. Щелкните мышью ячейку АЗ. Нажмите <Enter>. Такой способ ввода предпочтительнее, чем непосредственный набор адресов. Обратите также внимание на то, что формула в ячейке А5 использует результат вычисления формулы, размещенной в А4.

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

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

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. Нажмите кнопку ОК.

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 частично обрезан. Для изменения ширины столбца А необходимо поместить курсор между названиями столбцов А и В (курсор примет вид двунаправленной стрелки), нажать на левую клавишу мыши и, удерживая ее, сдвинуть границу столбца. Аналогичным образом изменяется ширина строки.

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

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

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

3) Измените ориентацию текста, разместив текст вертикально (Выравнивание>Ориентация).

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

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

1) Поместим в В1 число 4, а в В2 число 3. Введем в ячейку В3 формулу . В Excel она будет выглядеть так: =(1+х)/(4*у) или без использования имен: =(1+В1)/(4*В2). В ячейке В3 выводится результат 0,416667.

2) Введем в ячейку В4 формулу . В В4 введем =(х-2)/(5+(2*x)/(y^2+3)). В ячейке В4 выводится результат 0,352941.

3) В ячейку В6 введите формулу . Какое сообщение будет выведено в ячейке? Почему? Исправьте формулу, прибавив к знаменателю 1.

4) Вычислить в ячейке Е1. Для вычислений необходимо использовать функцию логарифма. Для ввода функций используем команды во вкладке Формулы или кнопку слева от строки ввода .

5) Откроется диалоговое окно Мастер функций – шаг 1 из 2. Слева – категория функции, справа – список функций внутри категории. Логарифм относится к математическим функциям, поэтому в левом окне выбираем строку Математические, справа находим функцию LOG. В нижней части окна появляется краткое описание функции:

LOG(число, основание_логарифма) возвращает логарифм числа по заданному основанию

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

6) Вложенные функции. Введите в ячейку E3 формулу для вычисления . Эта функция представляет собой композицию двух функций и . Соответствующие функции 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 для изменения типа ссылки (абсолютная/относительная/смешанная)