Практическая работа MS Excel

Создание сложной шапки таблицы и её форматирование.

Задание № 1. Запустите Excel.

Пуск Программы Microsoft Excel

Задание № 2. Сохраните файл с рабочей книгой под именем chapter3 на диске A: в папке book.

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

Итак, задача состоит в построении вот такой шапки:

Для построения такой шапки поступаем так:

1. выделите блок ячеек А3:А9;

2. щёлкните правой кнопкой мыши по выделенному блоку и в появившемся контекстном меню выберите пункт «Формат ячеек…» или войдите в меню «Формат» и выберите пункт «Ячейки…» ;

3. в открывшемся диалоговом окне «Формат ячеек», имеющем 6 вкладок, перейдите на вкладку «Выравнивание» и выполните все установки, показанные на рисунке ;

4. щёлкните по кнопке ОК, в итоге выделенный блок примет вид: .

Чтобы сделать эффективной работу, выделяйте последовательно следующие блоки для объединения ячеек и нажимайте клавишу F4, тогда все установки будут повторяться для этих блоков. Итак, следующим шагом будет выделение блока В3:С3 и нажатие клавиши F4. в итоге Вы получите: . (Старайтесь не ошибаться при выделении ячеек, иначе Вам клавиша F4 не поможет и тогда Вам придется повторить все действия, которые Вы выполняли при объединении ячеек А3:А9). Продолжайте объединение ячеек:

1. выделите блок ячеек В4:В9 и нажмите клавишу F4;

2. выделите блок ячеек С4:С9 и нажмите клавишу F4;

3. выделите блок ячеек D3:D9 и нажмите клавишу F4;

4. выделите блок ячеек E3:E9 и нажмите клавишу F4;

5. выделите блок ячеек F3:M3 и нажмите клавишу F4;

6. выделите блок ячеек F4:G5 и нажмите клавишу F4;

7. выделите блок ячеек H4:I5 и нажмите клавишу F4;

8. выделите блок ячеек J4:K5 и нажмите клавишу F4;

9. выделите блок ячеек L4:M5 и нажмите клавишу F4;

10. выделите блок ячеек F6:F9 и нажмите клавишу F4;

11. выделите блок ячеек G6:G9 и нажмите клавишу F4;

12. выделите блок ячеек H6:H9 и нажмите клавишу F4;

13. выделите блок ячеек I6:I9 и нажмите клавишу F4;

14. выделите блок ячеек J6:J9 и нажмите клавишу F4;

15. выделите блок ячеек K6:K9 и нажмите клавишу F4;

16. выделите блок ячеек L6:L9 и нажмите клавишу F4;

17. выделите блок ячеек M6:M9 и нажмите клавишу F4.

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

Далее нужно заполнить текстом все блоки таблицы. Сначала заполните текстом те блоки, в которых текст располагается горизонтально. Для этого:

1. поместите курсор в ячейку А3 (при этом выделится блок А3:А9) и введите текст «Наименование работы и материала»;

2. поместите курсор в ячейку В3 и введите текст: «Объём работ»;

3. поместите курсор в ячейку F3 и введите текст: «Расходы материалов»;

4. поместите курсор в ячейку F4 и введите текст: «План»;

5. поместите курсор в ячейку H4 и введите текст: «На выполненный объём работ»;

6. поместите курсор в ячейку J4 и введите текст: «Фактически»;

7. поместите курсор в ячейку L4, уменьшите размер шрифта до 9 пт и введите текст: «Результат: экономия (+), перерасход (-)»

Теперь заполните те блоки, в которых текст повёрнут на 90°. Для этого:

1. поставьте курсор мыши в ячейку В4, щёлкните по выделенному блоку правой кнопкой мыши и в появившемся контекстном меню выберите пункт «Формат ячеек…» ;

2. в диалоговом окне «Формат ячеек» перейдите на вкладку «Выравнивание» и к уже имеющимся установкам добавьте установку ориентации текста, перетащив левой кнопкой мыши красный указатель на 90°в разделе «Ориентация» , щёлкните по кнопке ОК;

3. введите текст «По плану»;

4. выделите все оставшиеся незаполненными блоки таблицы (выделение выполнять при нажатой клавише Ctrl);

5. нажмите клавишу F4;

6. поместите курсор в ячейку С4 и введите текст: «Фактически»;

7. поместите курсор в ячейку D3 и введите текст: «Нормы расхода»;

8. поместите курсор в ячейку E3 и введите текст: «Оптовые цены за единицу, р. – к.»;

9. поместите курсор в ячейку F6 и введите текст: «Количест-во» (перенос вставьте обязательно, чтобы программа правильно выполнила перенос слова);

10. нажмите сочетание клавиш Ctrl+Enter (в итоге Вы остались в той же ячейке);

11. нажмите сочетание клавиш Ctrl+Insert (тем самым Вы скопировали текст в буфер обмена);

12. поместите курсор в ячейку H6 и нажмите сочетание клавиш Shift+Insert (тем самым Вы вставили текст из буфера обмена в выделенный блок);

13. поместите курсор в ячейку J6 и нажмите сочетание клавиш Shift+Insert;

14. поместите курсор в ячейку L6 и нажмите сочетание клавиш Shift+Insert;

15. нажмите клавишу Esc;

16. поместите курсор в ячейку G6 и введите текст «Сумма,», нажмите сочетание клавиш Alt+Enter и продолжите ввод текста «р. – к.» (сочетание клавиш Alt+Enter позволяет выполнить принудительный перенос на новую строку в ячейке);

17. нажмите сочетание клавиш Ctrl+Enter;

18. нажмите сочетание клавиш Ctrl+Insert;

19. поместите курсор в ячейку I6 и нажмите сочетание клавиш Shift+Insert;

20. поместите курсор в ячейку K6 и нажмите сочетание клавиш Shift+Insert;

21. поместите курсор в ячейку M6 и нажмите сочетание клавиш Shift+Insert;

22. нажмите клавишу Esc.

Таким образом, мы заполнили таблицу, осталось её отформатировать. Сначала сделаем обрамление таблицы и сетку. Для этого:

1. выделите всю таблицу;

2. Формат Ячейки…;

3. В диалоговом окне «Формат ячеек» перейдите на вкладку «Граница» и выполните следующие действия: а) в поле «Тип линии» выберите мышкой правый нижний образец; б) в поле «Цвет» щёлкните по раскрывающей кнопке, и в палитре цветов выберите цвет Индиго (в первой строке палитры – предпоследний); в) щёлкните по кнопке «внешние»; г) в поле «Тип линии» выберите мышкой левый нижний образец; д) щёлкните по кнопке «внутренние» ;

4. Щёлкните по кнопке ОК.

Теперь выполните заливку блоков. Для этого:

1. выделите ячейку А3;

2. щёлкните по выделенному блоку правой кнопкой мыши и в появившемся контекстном меню выберите пункт «Формат ячеек…»;

3. в диалоговом окне «Формат ячеек» перейдите на вкладку «Вид»;

4. в палитре цветов выберите цвет Светло-жёлтый и щёлкните по кнопке ОК;

5. выделите ячейки В3 и F3 (при нажатой клавише Ctrl) и залейте их цветом Светло-зелёным;

6. выделите ячейки В4 и F4 и залейте произвольным цветом, используя дополнительно Узор;

7. выделите ячейки С4 и J4 и залейте их произвольным цветом;

8. выделите ячейки F6, H6, J6, L6 (не забывайте про клавишу Ctrl) и залейте их произвольным цветом с использованием Узора;

9. выделите ячейки G6, I6, K6, M6 (не забывайте про клавишу Ctrl) и залейте их произвольным цветом с использованием Узора;

10. оставшиеся ячейки залейте разными цветами по своему вкусу.

Название таблицы выполните с использованием объекта WordArt, как это Вы делали в Word, и расположите заголовок по центру таблицы.

Сохраните построенную шапку. А теперь самостоятельно на листе Лист2 постройте шапку, которая изображена ниже:

Построенную шапку сохраните в той же книге chapter3, щёлкнув по кнопке «Сохранить» на панели инструментов «Стандартная». Завершите работу в Excel.

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

§1. Арифметические формулы

Задание № 1. Запустите Excel.

Пуск Программы Microsoft Excel

Задание № 2. Сохраните файл с рабочей книгой под именем chapter4 на диске A: в папке book.

Задание №3. Дайте первому листу имя «Формулы» ()

Чтобы эффективно производить вычисления в Excel, Вы должны помнить следующие правила:

1. 1. Формула начинается со знака равенства;

   2. При вычислениях могут использоваться знаки операций: + - сложение, - - вычитание, * - умножение, / - деление, ^ - возведение в степень;

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

Вы должны будете ввести серию формул, зависящих от двух аргументов: x и y. Задание №4. Чтобы Вы выполнили задание более рационально, присвойте имена ячейкам, для этого:

1. в ячейку А1 введите букву «Х»;

2. в ячейку А2 введите букву «Y»;

3. выделите ячейку В1 и в поле имени (над столбцом А) появится адрес В1;

4. выделите мышью поле имени и наберите букву «х»;

5. нажмите клавишу Enter;

6. а теперь самостоятельно присвойте ячейке В2 имя «y»;

7. в ячейку В1 введите число 4;

8. в ячейку В2 введите число 3.

Задание №5. Ввод формул:

1. в ячейку В3 введите формулу : ( правильная формула: =(1+x)/(4*y) если бы ячейкам не были присвоены имена, то формулу надо было бы ввести так: =(1+B1)/(4*B2)). Правильный результат ;

2. в ячейку В4 введите формулу : . Должен получиться результат: 0,352941. Если такой результат не получился, проверьте ещё раз ввод формулы ;

3. в ячейку В5 введите формулу : . Должен получиться результат: -5.93548. Если такой результат не получился, проверьте ещё раз ввод формулы ;

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

§ 2. Использование функций.

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

Задание №6. Дайте второму листу имя «Функции» ()

Задание №7. Вычислите функцию log₃2+1 Для этого выполните следующие действия:

1. на листе «Функции» установите курсор в ячейку В3;

2. нажмите клавишу со знаком «=»;

3. в строке ввода формул щёлкните по кнопке ƒ_x или войдите в меню «Вставка» и выберите пункт «Функция…» , в итоге Вы получите окно первого шага Мастера функций: «Мастер функций – шаг 1 из 2»;

4. окно первого шага Мастера функций содержит три поля: «Поиск функции», «Категория» и «Выберите функцию» . Поле «Поиск функции» используется в том случае, если Вы точно не знаете название функции, но можете сказать, какие действия Вам необходимо выполнить. Кратко описав необходимые Вам действия, щёлкните по кнопке «Найти». Поле «Категория» помогает выбрать нужную группу функций в раскрывающемся списке (математические, логические, статистические и т.д.). Если Вы не можете отнести функцию к какой-либо категории, то обратитесь к пункту «Полный алфавитный перечень». В нашем примере надо щёлкнуть по категории «Математические». После этого в поле «Выберите функцию» появится список математических функций. Щёлкая мышью по полосе прокрутки этого поля, найдите функцию с именем LOG. Выделите её мышью. В нижней части окна появляется краткое описание функции: «LOG(число, основание_логарифма) возвращает логарифм числа по заданному основанию». (Если Вы хотите прочитать справку об этой функции, ознакомиться с примерами её использования, то щёлкните мышью по синему тексту «Справка по этой функции» слева внизу);

5. щёлкните по кнопке «ОК», в результате чего Вы перейдёте к диалоговому окну «Мастер функций – шаг 2 из 2»;

6. в этом окне два поля ввода для аргументов функции: «Число» и «Основание_логарифма» . Обратите внимание, что название первого аргумента выделено жирным шрифтом, а второго – обычным. Это означает, что первый аргумент обязательный, а второй можно опустить. Если Вы не укажите второй (необязательный) аргумент, то будет подразумеваться основание 10. В поле ввода «Число» введите число 2, а в поле «Основание» - число 3. Результат вычисления этой функции Вы уже увидите ниже полей ввода. Не спешите щёлкать по кнопке «ОК», ведь необходимо ещё прибавить к результату 1;

7. находясь во втором окне Мастера функций, щёлкните мышью в поле ввода формул сразу за сформированной частью формулы =LOG(2;3) и введите недостающее слагаемое +1. ;

8. нажмите клавишу Enter. Результат: 1,63093

Перечислим основные математические функции, соответствующие элементарным функциям, изучаемым в школьном курсе математики:

sin x

cos x

tg x

arcsin x

arcos x

SIN(X)

COS(X)

TAN(X)

ASIN(X)

ACOS(X)

arctg x

ln x

lg x

e^x

|x|

ATAN(X)

LN(X)

LOG(X)

EXP(X)

ABS(X)

У тригонометрических функций аргумент измеряется в радианах.

Задание №8. В ячейку В4 введите формулу для вычисления функции . Для этого:

1. выделите ячейку В4;

2. введите с клавиатуры знак «=»;

3. в поле ввода имени появилась панель функции. Щёлкните по раскры-вающей кнопке для выбора функции ;

4. перед Вами раскрылся список, в котором перечислены 10 недавно использовавшихся функций. Последний элемент списка называется «Другие функции…». Щёлкните по этому пункту мышью ;

5. в окне первого шага Мастера функций, в поле «Категория» выберите пункт «Математические», а в поле «Выберите функцию» выделите мышью функцию с именем ASIN и щёлкните по кнопке ОК;

6. в окне второго шага Мастера функций в поле ввода «Число» введите формулу: x/(x^2+1) и щёлкните по кнопке ОК;

7. Вы получили результат вычислений, но функцию эту надо откорректировать, так как надо ещё дописать второе слагаемое. Находясь в ячейке В4, нажмите клавишу F2 и Вы снова вернётесь в режим Ввода и в строке формул появится курсор;

8. Нажмите клавишу «+» и щёлкните по кнопке f_x в результате чего появится окно первого шага мастера функций;

9. В поле «Категория» установите – Математические, а поле «Выберите функцию» щёлкните по функции ATAN , а затем по кнопке ОК;

10. В окне второго шага Мастера функций в поле ввода «Число» введите формулу: y+2 и щёлкните по кнопке ОК. Результат .

А теперь рассмотрим, как можно вычислить вложенные функции. Рассмотрим формулу для вычисления функции . Эта функция представляет собой композицию двух функций: w = и z = tg x + 1. Соответствующие функции Excel: КОРЕНЬ(Z) и TAN(X)+1.

Задание №9. В ячейку В4 введите формулу для вычисления функции . Для этого:

1. выделите ячейку В5;

2. введите с клавиатуры знак «=»;

3. в строке ввода формул щёлкните по кнопке ƒ_x или войдите в меню «Вставка» и выберите пункт «Функция…» , в итоге Вы получите окно первого шага Мастера функций: «Мастер функций – шаг 1 из 2»;

4. в окне первого шага Мастера функций, в поле «Категория» выберите пункт «Математические», а в поле «Выберите функцию» выделите мышью функцию с именем КОРЕНЬ и щёлкните по кнопке ОК;

5. в окне второго шага Мастера функций, находясь в поле ввода аргумента функции КОРЕНЬ, в панели функций выберите пункт «Другие функции…» ;

6. в открывшемся диалоговом окне первого шага Мастера функций перейдите на Категорию - Математические, а в поле «Выберите функцию» найдите функцию TAN и щёлкните по кнопке ОК;

7. находясь в окне второго шага Мастера функций, в поле ввода аргумента этой функции введите имя Х , но не щёлкайте кнопку ОК;

8. установите в поле ввода курсор и щёлкните по функции КОРЕНЬ (т.е. по внешней функции). Тотчас второе окно Мастера функций для TAN заменяется на второе окно Мастера функций для функции КОРЕНЬ. В поле ввода аргумента отображается TAN(X) ;

9. щёлкните мышью в поле ввода «Число» и после появления текстового курсора наберите +1 и щёлкните по кнопке ОК. Результат: .

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

В Excel имеется функция ПИ(), она не имеет аргументов (хотя скобки обязательны) и возвращает число π= 314159… . В Excel для числа e=2,71828… (основание натуральных логарифмов) нет аналогичной функции, но для вычисления числа e нужно воспользоваться функцией EXP, взяв в качестве аргумента число 1, т.е. EXP(1).

12