Обработка данных в Microsoft Excel

Обработка данных в Microsoft Excel

Создание простейших таблиц. 1

Мастер функций. Форматирование ячеек. 2

Графическое представление результатов. 5

Относительная и абсолютная адресация. Условное форматирование. 6

Основные логические функции Microsoft Excel. 8

Создание простейших таблиц.

Задание 1.

1. Запустите Microsoft Excel и в созданной новой книге переименуйте листы: «Лист 1» – в «Задание 1» и т.д. Для этого используйте правую кнопку мыши.
2. Откройте лист «Задание 1». Задайте для столбца A ширину 20, а для столбцов B-N – 4.

Подсказка. В главном меню Microsoft Excel выберите пункт?, а в нем Вызов справки. Выберите вкладку Предметный указатель и введите с клавиатуры слово «столбцы». Появится список пунктов, по любому из которых Вы можете получить ответ. Выберите пункт «ширина» и прочитайте, как изменить ширину сразу нескольких столбцов.

1. Задайте для первой строки высоту 45, а для строк с номерами 2-12 – высоту 21.
2. В ячейке D2 получите число – возраст Екатеринбурга. Для этого в ячейки B2 и C2 введите соответственно год основания города (1723) и текущий год, а в ячейку D2 – формулу
   =C2-B2. Попробуйте ввести в ячейки B2 и C2 другие числа. Посмотрите, как реагирует на это содержимое ячейки D2. Запомните: любая формула в Excel должна начинаться со знака «=» и не должна содержать пробелов.
3. Выровняйте содержимое ячеек по центру. Для этого выделите их и используйте контекстное меню Формат ячеек… либо меню [Формат – Ячейки…].

 

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

1. На листе «Задание2» подготовьте таблицу следующего вида:

 

 

1. Создайте на листе «Задание2» три копии получившейся таблицы.

Подсказка. На Ваш вкус предлагается два способа копирования ячеек:

· Выделив таблицу, используйте меню [Правка – Копировать] и [Правка – Вставить].

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

 

1. На листе «Задание 3» подготовьте таблицу следующего вида:

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

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

· Для ячейки «Наименование предмета» установите флажок Переносить по словам (окно Формат ячеек, вкладка Выравнивание).

 

 

 

Мастер функций. Форматирование ячеек.

Задание 2.

1. Запустите программу Excel (Пуск>Программы >Microsoft Excel).

2. Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабочему листу имя Данные.

3. Дайте команду Файл > Сохранить как и сохраните рабочую книгу под именем book.xls.

4. Сделайте текущей ячейку А1, щелчком левой кнопки мыши и введите в нее заголовок «Результаты измерений».

5. Щелкните правой кнопкой мыши по названию столбца А и в появившемся меню выберите команду Ширина столбца и затем задайте ширину так, чтобы входила надпись.

 

6. Введите произвольные числа в последовательные ячейки столбца А, начиная с ячейки А2.

7. Введите в ячейку В1 строку «Удвоенное значение».

8. Введите в ячейку С1 строку «Квадрат значения».

9. Введите в ячейку D1 строку «Квадрат следующего числа».

10. Введите в ячейку В2 формулу =2*А2 (формулы вводятся на английском языке).

11. Введите в ячейку С2 формулу =А2*А2.

12. Введите в ячейку D2 формулу =В2+С2+1.

13. Выделите протягиванием ячейки В2,С2 и D2.

14. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и перетащите этот маркер, чтобы рамка охватила столько строк в столбцах В, С и D, сколько имеется чисел в столбце А.

15. Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значениями ячейки в столбце А текущей строки. Такое копирование формул называется автозаполнением.

16. Измените одно из значений в столбце А и убедитесь, что соответствующие значения в столбцах В, С и D в этой же строке были автоматически пересчитаны.

17. Введите в ячейку Е1 строку Масштабный множитель.

18. Введите в ячейку Е2 число 5.

19. Введите в ячейку F1 строку Масштабирование.

20. Введите в ячейку F2 формулу =А2*Е2.

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

22. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес Е2 в формуле задан относительной ссылкой.

23. Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$E$2, и нажмите клавишу ENTER.

24. Повторите заполнение столбца F формулой из ячейки F2.

25. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу book.xls.

Задание 2 (продолжение.)

1. Перейдите на Лист 2, введите следующую таблицу:

2. Теперь попробуем создать формулу с помощью вставки функции. При этом придерживаемся следующей последовательности действий:

1) Выделяем ячейку D2

2) Нажимаем кнопку Вставка функции .

3) Выберем функцию Произвед (Произведение).

4) Следующий шаг – выбор диапазона ячеек, которые будут перемножаться. Чтобы указать ячейки, убираем временно с экрана окно (для этого нажимаем на кнопочку справа). Указываем ячейки – выделяем мышью ячейки B2 и C2. Обратите внимание: если необходимо выделить ячейки, не расположенные рядом, то следует нажать клавишу Ctrl и, удерживая ее, выделять мышью ячейки.

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

6) Теперь результат вычислен и записан в ячейку. Обратите внимание: если выделить эту ячейку, в строке формул будет показана формула:

7) Таким же образом создадим формулы для ячеек D3 и D4. Для этого выделяем ячейку D3, вставляем функцию Произвед (теперь она находится в списке 10 недавно использовавшихся),

8) выделяем теперь уже ячейки B3 и C3, аналогично поступаем для ячейки D4, только в диапазоне указываем ячейки B4 и C4. Думаю, алгоритм понятен.

9) Выделим ячейки, которые необходимо просуммировать: D2, D3 и D4. Нажмем кнопку Автосумма.

3. Теперь оформим нашу таблицу.

1) Выделяем таблицу, в контекстном меню выбираем Формат ячеек, открываем закладку Границы и нажимаем кнопки Внешние и Внутренние. Таким образом, появятся линии между ячейками и вокруг ячеек.

2) Кроме этого можно изменить цвет самих ячеек. Для этого выделяем ячейки с названиями колонок и все в том же окне Формат ячеек в закладке Вид указываем цвет.

Вот что получится:

Задание 5.

Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10 000 — зеленым цветом шрифта; меньше 7000 красным; больше или равно 10 000 — синим цветом шрифта (Формат/Условное форматирование)

1. Проведите сортировку по фамилиям валфавитном порядке по возрастанию(меню Данные/Сортировка, сортировать по — Столбец В).

2. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания.

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

4. Защитите лист «Зарплата ноябрь» от изменений (Сервис/За щита/ Защитить лист ). Задайте пароль на лист, сделайте подтверждение пароля. Убедитесь что лист защищен и не возможно удаление данных. Снимите защиту листа (Сервис/Защита/ Снять Защиту листа).

Задание 7.

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

Исходными данными для расчета являются: Фамилия рабочего, тариф, количество рабочих дней.

Надо рассчитать:

1. Заработную плату - тариф * количество рабочих дней;

2. Районный коэффициент, зависящий от заработной платы:

Если зарплата <=3000, то районный коэффициент равен - 0,15 * зарплата

Если зарплата >3000, то районный коэффициент равен 45 руб.

3. Всего начислено - зарплата + районный коэффициент

4. Подоходный налог зависит от суммы всего начислено:

Если всего начислено <=150, то налог не начисляется, т.е. равен 0;

Если всего начислено <=2000, то налог равен 0,13*(всего начислено - 150);

Если всего начислено >2000, то налог равен 0,13*всего начислено.

5. Сумма к выдаче рассчитывается по формуле: всего начислено - налог.

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

Пример оформления задачи:

Ноябрь 2000 г.

№ п/п	Фамилия И.О.	тариф	Рабочие дни	зарплата	Районный коэфф.	Всего начислено	налог	Сумма к выдаче
	Аникеев А.Б.
	Бобров К.Э.
	Клюев Р.Б.
	Марков И.И.
	Попов В.Т.
					Итого:

 

 

Обработка данных в Microsoft Excel

Создание простейших таблиц. 1

Мастер функций. Форматирование ячеек. 2

Графическое представление результатов. 5

Относительная и абсолютная адресация. Условное форматирование. 6

Основные логические функции Microsoft Excel. 8

Создание простейших таблиц.

Задание 1.

1. Запустите Microsoft Excel и в созданной новой книге переименуйте листы: «Лист 1» – в «Задание 1» и т.д. Для этого используйте правую кнопку мыши.
2. Откройте лист «Задание 1». Задайте для столбца A ширину 20, а для столбцов B-N – 4.

Подсказка. В главном меню Microsoft Excel выберите пункт?, а в нем Вызов справки. Выберите вкладку Предметный указатель и введите с клавиатуры слово «столбцы». Появится список пунктов, по любому из которых Вы можете получить ответ. Выберите пункт «ширина» и прочитайте, как изменить ширину сразу нескольких столбцов.

1. Задайте для первой строки высоту 45, а для строк с номерами 2-12 – высоту 21.
2. В ячейке D2 получите число – возраст Екатеринбурга. Для этого в ячейки B2 и C2 введите соответственно год основания города (1723) и текущий год, а в ячейку D2 – формулу
   =C2-B2. Попробуйте ввести в ячейки B2 и C2 другие числа. Посмотрите, как реагирует на это содержимое ячейки D2. Запомните: любая формула в Excel должна начинаться со знака «=» и не должна содержать пробелов.
3. Выровняйте содержимое ячеек по центру. Для этого выделите их и используйте контекстное меню Формат ячеек… либо меню [Формат – Ячейки…].

 

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

1. На листе «Задание2» подготовьте таблицу следующего вида:

 

 

1. Создайте на листе «Задание2» три копии получившейся таблицы.

Подсказка. На Ваш вкус предлагается два способа копирования ячеек:

· Выделив таблицу, используйте меню [Правка – Копировать] и [Правка – Вставить].

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

 

1. На листе «Задание 3» подготовьте таблицу следующего вида:

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

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

· Для ячейки «Наименование предмета» установите флажок Переносить по словам (окно Формат ячеек, вкладка Выравнивание).