Раздаточный материал по excel

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

Тема
урока «Работа с электронными таблицами
Excel».

Раздаточный
материал для темы «Работа с формулами и функциями».

Составьте
таблицу для вычисления жизненной ёмкости лёгких.

Жизненная
ёмкость лёгких (ЖЕЛ) – ёмкость лёгких, соответствующая максимальному объёму
воздуха, вдыхаемому или выдыхаемому данным человеком одновременно. Это один из
показателей физического развития. ЖЕЛ – это количество воздуха, которое
индивидуум способен выдохнуть после максимально глубокого вдоха.

ЖЕЛ
зависит от пола, возраста, размеров тела, состояния тренированности.

Она
бывает в следующих пределах: у мужчин – 3,5-5,5 л; у женщин – 2,5-4,0 л. У
спортсменов эта величина может достигать: у мужчин  7,0 л и более, у женщин –
5,0 л и более. В отдельных случаях у людей очень высокого роста ЖЕЛ может
достигать 9,0 л . *

*
Данные
взяты из учебника М.Г.Гилярова «Математика для медицинских колледжей»

Расчётные
формулы для должной жизненной ёмкости лёгких:

Мальчики8-12
лет:  ДЖЕЛ (л) = Рост (см) *0,052 – Возраст (лет)*0,022 – 4,6

Мальчики
13-16 лет:  ДЖЕЛ (л) = Рост (см) *0,052 – Возраст (лет)*0,022 – 4,2

Девочки
8-16 лет: ДЖЕЛ (л) = Рост (см) *0,041 – Возраст (лет)*0,018 – 3,7

Взрослые
мужчины: ДЖЕЛ (л) = Рост (см) *0,052 – Возраст (лет)*0,022 – 3,6

Взрослые
женщины: ДЖЕЛ (л) = Рост (см) *0,041 – Возраст (лет)*0,018 – 2,68

Таблица
в готовом виде должна выглядеть следующим образом:

Так
как вычисляемая величина зависит от двух показателей (возраст и рост), значит их
и будем вносить в нашу таблицу.

 

.

1.      Выделим
ячейки от A1:
D7.

2.      Правой
кнопкой мыши выбираем Формат ячеек. Во вкладке Граница выбираем внешние
и внутренние, тип линии и цвет.

3.      В ячейке А1
пишем название нашей таблицы Расчёт должной жизненной ёмкости лёгких
(ДЖЕЛ).
Выделяем
A1:D1, выбираем «объединить и поместить в центре»

http://excel3161.narod.ru/img/shablon/shablon013.jpg

4.      Во второй
строке вносим названия величин, которые будем потом вносить: Категория,
возраст, рост, ДЖЕЛ. Выделяем вторую строку, правой кнопкой мыши выбираем Формат
ячеек
. Во вкладке Заливка выбираем нужный цвет.

5.      В ячейки А2
: А7 вносим категории, для каждой из них будет своя формула.

6.      В каждой
строке ДЖЕЛ вносим формулу, представленную выше, внимательно вносим отдельную
формулу для каждой категории. (например, для категории «мальчики 8-12 лет»
формула вносится такая =C3*0,052-B3*0,022-4,6  )

Используя
расчётную таблицу, решите задачи на вычисление ЖЕЛ:

1.      Определите ЖЕЛ для мужчины
30 лет ростом 180 см

2.      Определите ЖЕЛ для женщины
35 лет ростом 160 см

3.      Определите ЖЕЛ для девочки
12 лет ростом 145 см

4.      Определите ЖЕЛ для мальчика
8 лет ростом 120 см

Теперь
попробуйте составить таблицу для вычисления Жизненного индекса:

Жизненный
индекс = ЖЕЛ (мл) / масса тела (кг)

Обратите
внимание, что в данной формуле ЖЕЛ вносится в миллилитрах (!), а в нашей
предыдущей таблице – в литрах.

Раздаточный
материал для темы «Логические выражения».

1)      Создадим программу для
проверки знания умножения следующего вида. *

*Данный
пример был использован из Учебного пособия «
Exсel 2010 в примерах» Е.М.Карачевский,
И.Е.Филиппов, И.А.Филиппова.

1. Выделим
ячейки от A1:E12.

2. Правой
кнопкой мыши выбираем Формат ячеек. Во вкладке Граница выбираем внешние
и внутренние, тип линии и цвет.

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

4. В
ячейки B1 и A2:A11 можно вводить любые числа, а в ячейках B2:B11
вводим ответы. Это программа для самопроверки.

5. В
ячейку C1 введем слово ”проверка”.

6. В
ячейки C2 введем формулу =если(B2=B1*A2;”молодец”;”подумай”),
таким же образом в ячейки C3:C11. Это формула проверяет правильность
введенного ответа.

7. В
ячейку D1 введем слово “ошибка”.

8. В
ячейку D2 введем формулу =если(C2=»подумай»;1;0). Так
же для ячеек D3:D11 нужно написать подобную формулу. Если в проверке
было слово “подумай”, то в ошибке будет стоять единица. Это удобно чтобы
подсчи-тать общее количество ошибок.

9. В
ячейку D12 введем формулу, которое вычисляет общее количество оши-бок: =сумм(D2:D11).

10.В
ячейку E11 введем слово ”оценка”.

11.В
ячейку E12 введем формулу, которая будет оценивать ваше знание: =еcли(D12>3;”плохо”;если(D12=0;”отлично”;”хорошо”)).
Это формула для выставления оценки : если количество ошибок больше трех, то
оценка “плохо”, если ошибок нет то “отлично”, в других случаях “хорошо”.
Рас-смотрим пример, в котором есть ошибки:

 

2)     
Теперь, на основе проделанного выражения, создадим таблицу
для Критерия поиска пациентов с признаками анемии
, она должна выглядеть
так:

Сначала
внесём фамилии и значения гемоглобина

Фамилии

Гемоглобин, г/л

Признаки анемии

Сергеева

108

Петрова

112

Иванова

120

Петина

122

Федорова

126

Степашина

130

Александрова

135

Сидорова

137

Темина

141

Николаева

142

Далее,
в первое поле «признаки анемии» введём логическую формулу.

Если
гемоглобин <120, то «анемия», если нет, то «норма».

Логическую
формулу составьте самостоятельно на основании предыдущего упражнения.

(Формула:
=ЕСЛИ(B2<120;»анемия»;»норма»)         

Используя
маркер заполнения, заполните таблицу до конца.

Теперь,
используя «Фильтры», выявим  всех пациентов с признаками анемии.

·        
Выделим данные в столбце, далее «Данные» — «Фильтр»

·        
Нажимаем на выборку фильтра:

Выбираем
«числовые фильтры» — «меньше» — вписываем значение 120.

Получится
табличка, содержащая информацию только о пациентах с анемией:

Аналогично
можно выполнить «Текстовые фильтры» со словом «анемия».

Попробуйте.

Подготовлено
Преподавателем информатики
ГБПОУ
МО «МосОМК №1» Виноградовой С.В.

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

Вариант 1

Создание экзаменационной ведомости

I. Сформируйте структуру таблицы и заполните ее
значениями.

Экзаменационная ведомость

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

№ п/п

Фамилия, имя, отчество

№ зачетной книжки

Оценка

Подпись экзаменатора

1        
2        
       
10        

отлично _________________________
хорошо __________________________
удовлетворительно ________________
неудовлетворительно ______________
неявки ___________________________

ИТОГО __________________________

Расчет количества оценок

II. Рассчитайте:
1) Количество оценок и неявок, полученных в данной
группе (функция СЧЕТЕСЛИ).
2) Общее количество полученных оценок.
III. Назовите лист «Экзаменационная ведомость1».
Создайте две копии листа «Экзаменационная
ведомость1» и переименуйте их как
«Экзаменационная ведомость2» «Экзаменационная
ведомость3».
IV. Измените название дисциплин и часть оценок на
втором и третьем листе.

Создание ведомости на начисление
стипендии

V. Переименуйте четвертый лист в «Стипендия» и
создайте таблицу:

Ведомость назначения на стипендию

Группа № _________________
Стипендия ________________

№ п/п

Фамилия, имя, отчество

Средний балл

Количество сданных экзаменов

Стипендия

1        
2        
       
10        

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

VI. Перенесите фамилии студентов с любого из
трех первых листов с помощью трехмерной формулы.
VII. Подсчитайте значение среднего балла (функция
СРЗНАЧ) и количество сданных экзаменов (функция
СЧЕТ).
VIII. Вычислите размер стипендии по правилу
(функция ЕСЛИ):
1) если количество сданных экзаменов меньше трех,
стипендия не назначается,
2) если средний балл меньше 3, стипендия также не
назначается,
3) если средний балл больше 3, но меньше 5,
выплачивается обычная стипендия,
4) если средний балл больше 4,5, выплачивается
50%-ная премия к обычной стипендии.
IX. Подсчитайте сумму стипендиального фонда для
всей группы.
X. Установите автофильтр на столбец «Стипендия»
так, чтобы строки с нулевой стипендией не
выводились на экран.
XI. Постройте на отдельном листе гистограмму,
отображающую размер стипендии для каждого
студента.

I. II. III. IV. V. VI. VII. VIII. IX. X. XI.

Баллы

4

4

2

1

4

2

4

8

1

5

5

Вариант 2

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

I. Сформируйте структуру таблицы и заполните ее
значениями.

Ведомость сданной продукции

Наименование_____________________

№ п/п

Фамилия, имя, отчество

№ трудовой книжки

Количество

Сорт изделия

1        
2        
       
10        

Первый сорт (1) ________________
Второй сорт (2) _________________
Третий сорт (3) _________________
ИТОГО _______________________
Отсутствие продукции (нет) ______

Расчет количества продукции.

II. Рассчитайте:
1) Количество изделий разного сорта (функция
СУММЕСЛИ).
2) Общее количество полученных изделий.
3) Количество человек, не сдавших продукцию.
III. Назовите лист «Ведомость1». Создайте две копии
листа «Ведомость1» и переименуйте их как
«Ведомость2» и «Ведомость3».
IV. Измените количество, сорт и название продукции
на втором и третьем листе.

Создание ведомости на начисление
заработной платы.

V. Переименуйте четвертый лист в «Заработная
плата» и создайте таблицу:

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

Минимальная оплата __________________

№ п/п

Фамилия, имя, отчество

Средний сорт

Общее количество сданной продукции

Заработная плата

1        
2        
       
10        
    Итого    

VI. Перенесите фамилии работников с любого из
трех первых листов с помощью трехмерной формулы.
VII. Подсчитайте:
1) значение среднего сорта, округлив полученный
результат до целого числа,
2) и общее количество сданной продукции.
VIII. Вычислите размер оплаты по правилу (функция
ЕСЛИ):
1) если общее количество сданной продукции равно
нулю, оплата не производится,
2) если средний сорт равен 1, то одна единица
сданной продукции стоит 400 у.е.,
3) если средний сорт равен 2, то одна единица
сданной продукции стоит 350 у.е.,
4) если средний сорт равен 3, то одна единица
сданной продукции стоит 250 у.е.,
IX. Подсчитайте сумму фонда заработной платы для
всех работников.
X. Установите фильтр на столбец «Заработная
плата» так, чтобы строки с нулевой зарплатой не
выводились на экран.
XI. Постройте на отдельном листе круговую
диаграмму, отображающую размер зарплаты для
каждого работника.

I. II. III. IV. V. VI. VII. VIII. IX. X. XI.

Баллы

4

4

2

1

4

2

4

8

1

5

5

Вариант 3

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

I. Сформируйте структуру таблицы и заполните ее
постоянными значениями.

Список работников предприятия
Месяц ____________________
Зарплата за один день _______

№ п/п

Фамилия, имя, отчество

№ трудовой книжки

Количество отработанных дней

Зарплата

1        
2        
       
10        
    ИТОГО    

Расчет зарплаты.

II. Рассчитайте:
1) Общее количество отработанных дней за месяц,
2) Зарплату за указанное число дней в зависимости
от стоимости одного дня,
3) Общий фонд заработной платы.
III. Назовите лист «Список работников1». Создайте
две копии листа «Список работников1» и
переименуйте их как «Список работников2» и
«Список работников3».
IV. Измените количество отработанных дней,
минимальную зарплату и название месяцев на
втором и третьем листе.

Создание ведомости на расчет средней
заработной платы за 3 месяца.

V. Переименуйте четвертый лист в «Средняя
зарплата» и создайте таблицу:

Расчет средней заработной платы

№ п/п

Фамилия, имя, отчество

Разряд

Общее количество отработанных дней

Средняя заработная плата

Премия за разряд

1          
2          
         
10          
    Итого      

Первый разряд _______________
Второй разряд ________________
Третий разряд ________________
Четвертый разряд _____________
Пятый разряд _________________

VI. Перенесите фамилии работников с любого из
трех первых листов.
VII. Подсчитайте:
1) общее количество отработанных дней,
2) среднее значение заработной платы,
3) количество работников первого разряда, второго
разряда и т.д. (функция СЧЕТЕСЛИ).
III. Вычислите размер премии по правилу (функция
ЕСЛИ):
1) если разряд первый, то премия равна двум
средним заработным платам,
2) если разряд второй, то премия равна 1,5 от
средней заработной платы,
3) если разряд третий, то премия равна средней
заработной плате,
4) если разряд четвертый, то премия равна половине
средней заработной платы,
5) при пятом разряде премия не начисляется.
IV. Подсчитайте итоговые суммы для всех
работников.
V. Установите фильтр на столбец «Премия», чтобы
строки с нулевой премией не выводились на экран.
VI. Постройте на отдельном листе линейную
гистограмму, отображающую размер премии для
каждого работника.

I. II. III. IV. V. VI. VII. VIII. IX. X. XI.

Баллы

4

4

2

1

4

2

4

8

1

5

5

Вариант 4

Создание ведомости на получение
заработной платы.

I. Сформируйте структуру таблицы и заполните ее
постоянными значениями.

Ведомость сданной продукции
Месяц __________________

№ п/п

Фамилия, имя, отчество

№ трудовой книжки

Количество

1      
2      
     
10      
    ИТОГО  

Расчет зарплаты.

II. Рассчитайте:
1. Общее количество полученных изделий.
III. Назовите лист «Ведомость1». Создайте две копии
листа «Ведомость1» и переименуйте их как
«Ведомость2» и «Ведомость3».
IV. Измените значение количества изделий и
название месяца на втором и третьем листе.

Создание ведомости на начисление зарплаты

V. Переименуйте четвертый лист в «Зарплата» и
создайте таблицу:

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

Наименование изделия ___________________
Стоимость одного изделия ________________

№ п/п

Фамилия, имя, отчество

Общее количество изделий

Планируемое количество изделий за
месяц

Зарплата

1        
2        
       
10        
  Итого      

Без премии _______________
10% премия ______________
15% премия ______________
Без зарплаты _____________

VI. Перенесите фамилии работников с любого из
трех первых листов.
VII. Подсчитайте общее количество изделий за три
месяца и заполните столбец «Планируемое
количество изделий» произвольными числами.
VIII. Вычислите размер зарплаты по правилу:
1) если количество сданных изделий хотя бы в одном
месяце меньше планируемого, то оплачивается
только общее количество изделий за три месяца,
2) если общее количество изделий совпадает с
планируемым количеством в каждом месяце, то к
зарплате прибавляется премия в размере 10% от
начисленной зарплаты,
3) если общее количество изделий хотя бы в одном
месяце превышает планируемое, то к зарплате
прибавляется премия в размере 15% от начисленной
зарплаты.
IX. Подсчитайте:
1) значения в строке Итого,
2) количество человек, не получивших премию,
зарплату, получивших разные премии.
X. Установите фильтр на столбец «Зарплата» так,
чтобы строки с нулевой зарплатой не выводились
на экран.
XI. Постройте на отдельном листе коническую
диаграмму, отображающую размер зарплаты для
каждого работника.

I. II. III. IV. V. VI. VII. VIII. IX. X. XI.

Баллы

4

4

2

1

4

2

4

8

1

5

5

Основные типы и форматы данных

Типы

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

Текст – это последовательность символов, состоящая из букв, цифр и пробелов.

      По умолчанию текст выравниваются в ячейке по левому краю.

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

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

                                        

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

Ссылки

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

        Существуют следующие типы ссылок:

Относительные.

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

Абсолютные.

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

Смешанные

В смешанных ссылках координата столбца относительная, а строка — абсолютная(Например, A$1), или наоборот, координата столбца абсолютная, а строка – относительная (Например, $A1)

Упражнение.

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

Перейти к содержанию

На чтение 10 мин Опубликовано 21.03.2020

Ищите примеры работы в Excel? Хотите найти понятную инструкцию, которая позволит в совершенстве овладеть великим множеством инструментов Excel, причем быстро и легко? Предлагаем Вашему вниманию самоучитель по работе в Excel на простых примерах. Здесь Вы найдёте ответы на самые различные вопросы и научитесь применять Excel на практике. Самоучитель состоит из пяти разделов:

  • Введение
  • Основы
  • Функции
  • Анализ данных
  • VBA

Каждый раздел содержит несколько глав. В каждой главе вы найдете ознакомительный урок, раскрывающий основные понятия и положения, и несколько простых и понятных примеров. Хотите узнать об Excel намного больше? Немедленно приступайте к изучению Microsoft Excel вместе с Офис-Гуру! С нами вам будет гораздо проще!

Данный курс по Excel – это перевод очень популярного англоязычного сайта-самоучителя – excel-easy.com, который посещают десятки тысяч человек ежедневно! Думаю, что и вас он не оставит равнодушными!

Содержание

  1. Введение
  2. Основы
  3. Функции
  4. Анализ данных
  5. VBA

Введение

Этот раздел предназначен для пользователей, не знакомых с Excel.

Microsoft Excel – одно из самых широко используемых приложений в истории. Сотни миллионов людей во всём мире пользуются Microsoft Excel. Excel умеет работать с данными любого типа и выполнять финансовые, математические и статистические вычисления.

  1. Диапазон: Диапазон в Excel – это набор из двух или более ячеек. В этой главе Вы найдёте обзор некоторых очень важных операций с диапазонами.Примеры: Последовательность Фибоначчи, Пользовательские списки, Примечания, Скрытие строк и столбцов, Пропускать пустые ячейки, Транспонирование, Объединение и пересечение.
  2. Формулы и функции: Формула – это выражение, которое вычисляет значение ячейки. Функции – это предопределённые формулы, доступные в Excel.Примеры: Процентное изменение, Имена в формулах, Динамический именованный диапазон, Параметры вставки, Строка состояния, Быстрые операции.

Основы

Этот раздел даёт базовые понятия по работе в Excel.

  1. Лента меню: Когда Вы запускаете Excel, на Ленте меню открывается вкладка Главная. Узнайте, как сворачивать и настраивать Ленту.Примеры: Панель быстрого доступа, Вкладка Разработчик.
  2. Рабочая книга: Книгой называют файл Excel. Когда Вы запускаете Excel, автоматически создаётся пустая рабочая книга.Примеры: Сохраняем в формате Excel 97-2003, Просмотр нескольких рабочих книг, Рабочая область, Автовосстановление.
  3. Рабочий лист: Лист – это набор ячеек, в которых Вы храните и обрабатываете данные. По умолчанию каждая рабочая книга Excel содержит три листа (в Excel 2010 и более ранних версиях).Примеры: Масштаб, Разделяем лист, Закрепление областей, Группировка листов, Консолидация, Просмотр нескольких листов, Проверка правописания.
  4. Форматирование ячеек: При форматировании ячеек в Excel, мы изменяем лишь внешний вид содержимого, не изменяя самого значения.Примеры: Десятичные знаки, Денежный против финансового, Форматы даты и времени, Дроби, Текст в число, Число в текст, Пользовательские числовые форматы, Формат по образцу, Стили ячеек, Темы.
  5. Найти и выделить: В этой главе Вы научитесь использовать два очень полезных инструмента Excel: Найти и заменить и Перейти.Примеры: Особенности инструмента «Найти», Удаляем пустые строки, Отличия по строкам, Копируем только видимые ячейки, Строка поиска.
  6. Шаблоны: Вместо того, чтобы создавать рабочую книгу Excel с чистого листа, Вы можете создать рабочую книгу на базе шаблона. Существует множество бесплатных шаблонов, которые так и просятся, чтобы их использовали.Примеры: Календарь, Бюджет, Планировщик питания, Счет-фактура, Автоматизированный счет-фактура, Шаблоны по умолчанию.
  7. Проверка данных: Используйте проверку данных в Excel и будьте уверены, что пользователи введут в ячейку только подходящее значение.Примеры: Отбросить недопустимые даты, Ограничение бюджета, Предотвращение дублирования записей, Коды продуктов, Выпадающий список, Зависимые выпадающие списки.
  8. Сочетания клавиш: Сочетания клавиш позволяют увеличивать скорость работы, используя клавиатуру вместо мыши.Примеры: Функциональные клавиши, Подсказки клавиш.
  9. Печать: Эта глава научит вас отправлять листы Excel на печать и задавать некоторые важные настройки при печати.Примеры: Режимы просмотра, Разрывы страниц, Верхние и нижние колонтитулы, Номера страниц, Печать заголовков, Центрирование на странице, Печать сетки и заголовков строк/столбцов, Область печати.
  10. Обмен данными и общий доступ: Узнайте, как можно отправить данные Excel в документ Word или в другие файлы. Кроме этого, из множества приведенных примеров вы узнаете, как предоставить общий доступ к книге Excel.Примеры: Лист Excel в Word, Сохранить в PDF, Общий доступ к книге, SkyDrive, Excel Online, Импорт данных из Access, Microsoft Query, Импорт и экспорт текстовых файлов, XML.
  11. Защита: Зашифруйте файл Excel при помощи пароля. Только зная пароль, можно будет открыть этот файл.Примеры: Защита книги, Защита листа, Блокировка ячеек, Книга только для чтения, Пометить как окончательную.

Функции

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

  1. СЧЁТ и СУММ: Чаще всего в Excel используются функции, которые подсчитывают количество и сумму. Вы можете посчитать количество и сумму значений, выбранных по одному или по нескольким критериям.Примеры: Подсчёт вхождений текста, Подсчёт логических значений, Подсчёт пустых и непустых ячеек, Функция СУММПРОИЗВ.
  2. Логические функции: Узнайте, как пользоваться логическими функциями Excel, такими как ЕСЛИ, И, ИЛИ.Примеры: Вложенные функции ЕСЛИ, Игра «Бросить кости».
  3. Ссылки на ячейки: Ссылка на ячейку – это очень важный элемент Excel. Поймите разницу между относительной, абсолютной и смешанной ссылками, и Вы наверняка добьётесь успеха!Примеры: Копируем точную формулу, 3D ссылка, Создание внешней ссылки, Гиперссылки.
  4. Дата и время: Чтобы ввести дату в Excel, используйте символы-разделители: слеш (/), тире (-) или точку (.). Чтобы ввести время, используйте двоеточие (:). Дату и время можно вводить в одну ячейку.Примеры: Функция РАЗНДАТ, Будние и рабочие дни, Дней до дня рождения, Табель, Последний день месяца, Праздники, Квартал, День года.
  5. Текстовые функции: Excel предлагает огромное количество функций для самых различных операций с текстовыми строками.Примеры: Разделение строки, Количество экземпляров текста, Количество слов, Текст по столбцам, Верхний и нижний регистр, Удалить ненужные символы, Сравнение текста, Функции НАЙТИ и ПОИСК, Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ.
  6. Функции поиска и ссылок: Узнайте всё о функциях Excel для работы со ссылками и массивами, таких как ВПР, ГПР, ПОИСКПОЗ, ИНДЕКС и ВЫБОР.Примеры: Налоговые ставки, Функция СМЕЩ, «Левый” поиск, Двумерный поиск, Поиск максимального значения, Функция ДВССЫЛ.
  7. Финансовые функции: Этот раздел рассказывает о наиболее популярных финансовых функциях Excel.Примеры: Кредиты различной длительности, Инвестиции и аннуитет, Расчёт сложных процентов, График погашения кредита, Амортизация.
  8. Статистические функции: Здесь Вы найдёте обзор самых полезных статистических функций Excel.Примеры: Отрицательные числа на нули, Случайные числа, Функция РАНГ, ПЕРСЕНТИЛЬ и КВАРТИЛЬ, ПРЕДСКАЗ и ТЕНДЕНЦИЯ.
  9. Округление: В этом разделе описаны три самых популярных функции для округления чисел в Excel. Это функции ОКРУГЛ, ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ.Примеры: Отбрасываем десятичные знаки, Ближайшее кратное, Четные и нечетные.
  10. Ошибки в формулах: Эта глава научит Вас справляться с некоторыми распространёнными ошибками в формулах Excel.Примеры: ЕСЛИОШИБКА, ЕОШИБКА, Циклическая ссылка, Зависимости формул, Ошибка плавающей запятой.
  11. Формулы массива: Изучив эту главу, Вы будете понимать, что такое формулы массива в Excel. Одноячеечные формулы массива позволяют выполнять сразу серию вычислений в одной ячейке.Примеры: Подсчёт ошибок, Подсчёт уникальных значений, Подсчёт с критерием «ИЛИ», Суммирование каждой n-ой строки, Суммирование наибольших чисел, Суммирование диапазона с ошибками, Суммирование с критерием «ИЛИ», Поиск в двух столбцах, Наиболее часто встречающееся слово, Система линейных уравнений.

Анализ данных

Эта глава рассказывает о мощных инструментах, которые предлагает Excel для анализа данных.

  1. Сортировка: В Excel Вы можете сортировать по одному или нескольким столбцам. Расположите данные по убыванию или по возрастанию.Примеры: Сортировка по цвету, Обратный список, Случайный список.
  2. Фильтрация: Настройте фильтр для данных в Excel, чтобы видеть только записи, удовлетворяющие определённому критерию.Примеры: Числовые и текстовые фильтры, Фильтры по дате, Расширенный фильтр, Форма данных, Удаляем дубликаты, Структурирование данных.
  3. Условное форматирование: Условное форматирование в Excel позволяет выделить ячейки определённым цветом в зависимости от содержащихся в них значений.Примеры: Управление правилами, Гистограммы, Цветовые шкалы, Наборы значков, Новое правило, Поиск дубликатов, Закрашивание чередующихся строк, Сравнение двух списков, Конфликт правил, Чеклист.
  4. Диаграммы: Простейшая диаграмма в Excel может быть более красноречивой, чем таблица полная чисел. Вы увидите: диаграммы – это не сложно.Примеры: Гистограмма, График, Круговая диаграмма, Линейчатая диаграмма, Диаграмма с областями, Точечная диаграмма, Ряды данных, Оси, Лист диаграммы, Линия тренда, Предел погрешностей, Спарклайны, Комбинированная диаграмма, Диаграмма-спидометр, Диаграмма-термометр, Диаграмма Ганта, Диаграмма Парето.
  5. Сводные таблицы: Сводные таблицы – один из самых мощных инструментов Excel. Сводная таблица позволит выделить суть из огромного набора данных.
  6. Таблицы: Таблицы нужны для того, чтобы выполнять анализ имеющихся данных быстро и легко.
  7. Анализ «Что-если»: Анализ «Что-если» в Excel позволяет подставлять различные значения (сценарии) в формулы.
  8. Поиск Решения: В арсенале Excel есть инструмент, который называется «Поиск Решения». Он использует различные методы анализа вычислений для поиска наилучших решений для задач оптимизации всех видов.
  9. Пакет анализа: Пакет анализа – это надстройка Excel, которая предоставляет инструменты для анализа финансовых, статистических и технических данных.

VBA

VBA (Visual Basic for Applications) – это название языка программирования для Microsoft Office. В данном разделе описаны возможности и примеры использования VBA применительно к Excel.

  1. Создание макроса: При помощи VBA Вы можете автоматизировать задачи в Excel, записывая так называемые макросы. В этом разделе Вы научитесь создавать простой макрос.
  2. MsgBox: MsgBox – это диалоговое окно в VBA, при помощи которого можно показывать информационные сообщения пользователям Вашей программы.
  3. Объекты Workbook и Worksheet: Узнайте больше об объектах Workbook и Worksheet в VBA.
  4. Объект Range: Объект Range – это ячейка (или ячейки) Вашего листа. Это самый важный объект VBA.
  5. Переменные: В этом разделе Вы научитесь объявлять, инициализировать и отображать переменную в VBA.
  6. Выражение «If Then»: Используйте выражение «If Then» в VBA, чтобы выполнять строки кода, если встречается определённое условие.
  7. Цикл: Циклы – это мощнейшая из техник программирования. Цикл в VBA позволяет Вам обрабатывать диапазон ячеек снова и снова, написав для этого всего лишь несколько строк кода.
  8. Ошибки макросов: Этот раздел научит справляться с ошибками макросов в Excel.
  9. Операции со строками: В этом разделе Вы найдёте сведения о самых важных функциях для работы со строковыми элементами в VBA.
  10. Дата и время: Научитесь работать с датой и временем в VBA.
  11. События: События – это действия, совершаемые пользователями, которые запускают выполнение кода VBA.
  12. Массив: Массив – это группа переменных. В VBA Вы можете сослаться на определённую переменную (элемент) массива, используя для этого имя массива и числовой индекс элемента.
  13. Function и Sub: В Visual Basic for Applications функция (Function) может возвращать значение, в то время как процедура (Sub) – не может.
  14. Объект Application: Главным в иерархии объектов является сам Excel. Мы называем его объект Application. Объект Application открывает доступ ко многим параметрам, связанным с самим приложением Excel.
  15. Элементы управления ActiveX: Научитесь создавать элементы управления ActiveX, такие как кнопки, текстовые поля, поля со списком и так далее.
  16. Userform: В этой главе Вы узнаете, как создавать пользовательские формы в VBA.

Оцените качество статьи. Нам важно ваше мнение:

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

Практическая
работа №1. Простые вычисления.

Задание
1.1

Создайте
книгу

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

Стоимость программного обеспечения

наименование

стоимость, $

стоимость, руб.

стоимость, €

ОС windows

1180

пакет MS Office

320

бухгалтерия

500

Антивирус DR Web

200

Пакет OpenOffice

350

итого

Курс валюты (к рублю)

1.     
Записать
исходные текстовые и числовые данные, оформить таблицу согласно образцу,
приведенному выше.

2.     
Рассчитать 
«Стоимость, руб.», используя курс доллара как абсолютный адрес.

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

4.     
Рассчитать
графу «Итого», используя функцию =СУММ (выделить диапазон).

Задание
1.2

В
книге
 Практическая
работа в
Excel.

  1. Создайте
    таблицу учета товаров, на втором Листе книги, пустые столбцы
    сосчитайте по формулам.

курс доллара

63,5

Таблица учета проданного
товаров


пп

название

поставлено

продано

осталось

цена
в рублях за 1 товар

цена
в долларах за 1 товар

всего
в рублях

1

товар
1

50

43

170

2

товар
2

65

65

35

3

товар
3

50

43

56

4

товар
4

43

32

243

5

товар
5

72

37

57

Всего 

2.     
Отформатируйте
таблицу по образцу. Курс доллара- абсолютный адрес.

3.     
Переименуйте
лист Учет товара.

4.     
Оформите
таблицу (цвет шрифта, заливка, рамка таблицы)

5.     
Сохраните
работу в собственной папке.

Задание
1.3

В
книге
 Практическая
работа в
Excel.

1.     
Составьте
таблицу для выплаты заработной платы для работников предприятия на третьем
Листе
книги.

Расчет
заработной платы.

№ п/п

Фамилия, И.О.

Полученный доход

Налоговые вычеты

Налогооблагаемый
доход

Сумма налога,

НДФЛ

К выплате

1

Попов
В.И.

18000

1400

2

Богданов
К.М.

9000

1400

3

Суховой
П.Е.

7925

0

4

Копцева
Е.В.

40635

2800

5

Ермак
А.А.

39690

1400

6

Шпак
Г.С.

19015

2800

Итого 

  1. Сосчитайте по
    формулам пустые столбцы.
  1. Налогооблагаемый
    доход = Полученный доход – Налоговые вычеты.
  2. Сумма налога
    = Налогооблагаемый доход*0,13.
  3. К выплате =
    Полученный доход-Сумма налога НДФЛ.
  4. Отсортируйте
    таблицу в алфавитном порядке.
  5. Переименуйте
    лист Расчет заработной платы.
  6. Оформите
    таблицу (цвет шрифта, заливка, рамка таблицы)
  7. Сохраните
    работу в собственной папке.

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

Практическая
работа №2. Использование функций СУММ, СРЗНАЧ, МИН, МАКС, ЕСЛИ.

Задание 1.1

В
книге
 Практическая
работа в
Excel №2.

Заданы стоимость 1
кВт/ч электроэнергии и показания счетчика за предыдущий и текущий месяцы.
Необходимо вычислить расход электроэнергии за прошедший период и стоимость
израсходованной электроэнергии.

https://sites.google.com/site/rabotavexcel2007/_/rsrc/1467140662721/prakticeskaa-rabota-4/7.JPG

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

1. Выровняйте
текст в ячейках. Выделите ячейки А3:Е3. Главная — Формат –Формат ячейки –
Выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение
– переносить по словам.

2.  В ячейку
А4 введите: Кв. 1, в ячейку А5 введите: Кв. 2. Выделите ячейки А4:А5 и с
помощью маркера автозаполнения заполните нумерацию квартир по 7 включительно.

5.  Заполните
ячейки B4:C10 по рисунку.

6.  В
ячейку D4 введите формулу для нахождения расхода эл/энергии. И заполните строки
ниже с помощью маркера автозаполнения.

7.  В
ячейку E4 введите формулу для нахождения стоимости эл/энергии. И заполните
строки ниже с помощью маркера автозаполнения.

Обратите внимание! 
При автозаполнении адрес ячейки B1 не меняется, 
т.к. установлена абсолютная ссылка.

8.  В
ячейке А11 введите текст «Статистические расчеты» выделите ячейки A11:B11 и
щелкните на панели инструментов кнопку «Объединить и поместить в центре».

9.  В
ячейках A12:A15 введите текст, указанный на рисунке.

10.  В
ячейке B12 с помощью функции СУММ, рассчитать общую сумму стоимости
эл/энергии.

11.  Аналогично
функции задаются и в ячейках B13:B15.

В13-СРЗНАЧ расхода
эл/энергии,

В14-МАКС расход
эл/энергии,

В15-МИН расход
эл/энергии.

12.  Расчеты
выполняются на Листе 1, переименуйте его в Электроэнергию.

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

Функция ЕСЛИ
позволяет определить выполняется ли указанное условие. Если условие истинно, то
значением ячейки будет выражение1, в противном случае – выражение2.

Синтаксис функции

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)

Пример:  Вывести в ячейку
сообщение «тепло», если значение ячейки
B2>20, иначе
вывести «холодно»                                   =ЕСЛИ(
B2>20;”тепло”;”холодно”)

Пример: вывести сообщение
«выиграет» если значение ячеек Е4<3 и Н98>=13 (т.е. одновременно
выполняются условия), иначе вывести «проиграет»

                                                    
=ЕСЛИ(И(
E4<3;H98>=13);”выиграет”;”проиграет”)

Часто на практике одного условия
для логической функции мало. Когда нужно учесть несколько вариантов принятия
решений, выкладываем операторы ЕСЛИ друг в друга. Таким образом, у нас
получиться несколько функций ЕСЛИ в Excel.

Синтаксис будет выглядеть следующим
образом:

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

Здесь оператор проверяет два
параметра. Если первое условие истинно, то формула возвращает первый аргумент –
истину. Ложно – оператор проверяет второе условие.

Пример:

Вложение логических функций.

Задание 1.2

1.      Заполнить таблицу
и отформатировать по образцу (Лист 2 «Экзамены»)

2.      Заполните формулой
=СУММ диапазон ячеек
F4:F10

3.      В ячейках
диапазона
G4:G10 должно быть
выведено сообщение о зачислении абитуриента.

4.      Абитуриент
зачислен в институт, если сумма баллов больше или равна проходному баллу и
оценка по математике 4 или 5, в противном случае – не зачислен.

Задание
1.3 (Самостоятельная работа)

1.
Создайте таблицу оклада работников предприятия на Листе 3
(«Оклад»)
книги.

Оклад работников предприятия

статус

категория

оклад

премии

начальник

1

15 256,70р.

5
000,00р.

инженеры

2

10 450,15р.

4
000,00р.

рабочие

3

5 072,37р.

3
000,00р.

2.
Ниже создайте таблицу для вычисления заработной платы работников предприятия.


Заработная плата
работников предприятия

№ п/п

фамилия рабочего

категория рабочего

оклад рабочего

ежемесяч ные премии

подоход ный налог (ПН)

заработная плата

(ЗП)

1

Иванов

3

2

Петров

3

3

Сидоров

2

4

Колобков

3

5

Коврижкин

3

6

Алексеева

3

7

Королев

2

8

Боготырев

2

9

Морозов

1

10

Еремина

3

Итого

3. Оклад рабочего зависит от категории, используйте
логическую функцию ЕСЛИ для трех условий.

4. Ежемесячная премия рассчитывается таким же образом.

5. Подоходный налог  считается по формуле: ПН=(оклад+премяя)*0,13.

6. Заработная плата по формуле: ЗП=оклад+премия-ПН.

7. Отформатируйте таблицу по образцу.

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

Практическая
работа №3. Формат ячеек. Построение графиков

Задание
1.1

Запустить
табличный процессор
MS Office Excel

Оформить
таблицу согласно представленному ниже образцу

Выделить
диапазон ячеек В3:
G11. По выделенному диапазону нажимаем 1 раз
ПКМ.

Выбираем
пункт меню Формат ячеек на вкладке Число
выбираем пункт Денежный
-> ОК

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

В
ячейку G3 ввести формулу, которая будет рассчитывать заработок Алексея за 5
месяцев.

Диапазон
ячеек G4:G10 заполняется с помощью процедуры автозаполнения.

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

Диапазон
ячеек  В11:G11 заполняется с помощью процедуры автозаполнения.

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

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

Все
диаграммы должны быть на одном листе.

Для
этого необходимо выделить диапазон А3:В10

Вкладка
«Вставка», группа инструментов «Диаграмма», Круговая

После
выполнения действия результат:

Далее
необходимо написать имя диаграммы: выделяем диаграмму (щелкаем по ней 1 раз
ЛКМ), далее вкладка «Макет», группа инструментов «Подписи»,
название диаграммы

Выбираем
«Над диаграммой». Вводим в появившейся рамке на диаграмме «заработная
плата за январь».

Результат:

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

Далее
необходимо подписать данные: выделяем диаграмму (щелкаем по ней 1 раз ЛКМ),
далее вкладка «Макет», группа инструментов «Подписи», «Подписи
данных»

Выбираем
«У вершины, снаружи»

Результат:

Далее
необходимо изменить местоположение легенды (подпись данных): выделяем диаграмму
(щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи»,
«Легенда»

Выбираем
«Добавить легенду снизу»

Результат:

Необходимо
построить круговую диаграмму, отражающую зарплату Алексея за 5 месяцев

Для
этого выделяем диапазон ячеек
B2:F2 Вкладка
«Вставка», группа инструментов «Диаграмма», Круговая

После
выполнения действия результат:

Необходимо
подписать данные в процентах.

Чтобы подписать данные в процентах необходимо выделить
диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа
инструментов «Подписи», «Подписи данных», «Дополнительные параметры
подписи данных».

Ставим галочку «Доли», снимаем галочку «Значения».
Нажать «Закрыть».

Результат:

Задание 1.2 Построение
рисунка «ЗОНТИК»

План работы:

Приведены функции,
графики которых участвуют в этом изображении:

х  [-12;12]

с шагом 1

у1 = — 1/18х2 +
12,   х
[-12;12]

y2 = — 1/8х2 +
6,   х

[-4;4]

y3 = — 1/8(x+8)2 +
6,   х
[-12; -4] 

y4 = — 1/8(x-8)2 +
6,   х
[4; 12]

y5 = 2 (x+3)2 
 9,   х
[-4;0]

y6 = 1.5 (x+3)2  – 10,  
х
[-4;0]

Ход работы:

Запустить MS EXCEL

В
ячейке А1 внести обозначение переменной х

Заполнить
диапазон ячеек А2:А26 числами с -12 до 12 (автозаполнение).

Последовательно
для каждого графика функции будем вводить формулы.

Для
 у1= -1/8х2 + 12, х
[-12;12],
для 
y2= -1/8х2 +6,
х
[-4;4] и т.д.

Порядок выполнения действий:

1.                             
Устанавливаем
курсор в ячейку В1 и вводим у1

2.                             
В
ячейку В2 вводим формулу  = (-1/18)*A2^2 +12

3.                             
Нажимаем Enter на клавиатуре

4.                             
Автоматически
происходит подсчет значения функции.

5.                             
Растягиваем
формулу до ячейки А26.

6.                             
Аналогично
в ячейку С10 (т.к значение функции находим только на отрезке
х от [-4;4])  вводим формулу для графика функции 
y2 = (-1/8)*A10^2 +6   и
т.д.

В результате должна получиться следующая ЭТ:

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

1.                             
Выделяем
диапазон ячеек А1:
G26.

2.                             
На
панели инструментов выбираем меню Вставка → Диаграммы.

3.                             
В
окне Мастера диаграмм выберите Точечная → Точечная с
прямыми отрезками и маркерами или Точечная с гладкими прямыми→ Нажать 
Ok.

В
результате должен получиться следующий рисунок:

Точечная с
прямыми отрезками и маркерами

ИЛИ

Точечная с гладкими прямыми

Задание 1.3 (Самостоятельная
работа)
Построение рисунка «ОЧКИ».

Постройте графики
функций в одной системе координат. 

Х от -9 до 9 с
шагом 1
.
 Получите рисунок «Очки».

Х  [-9;9]

с шагом 1

у1 = -1/16(Х+5)2+2,  
х
[-9;-1]

y2 = -1/16(Х-5)2+2,
х
[1;9]

y3 = 1/4(Х+5)2-3, 
х
[-9;1]

y4 = 1/4(Х-5)2-3, 
х
[1;9]

y5 = — (Х+9)2+1, 
х
[-9;6]

y6 = -(Х-9)2+1, 
х
[6;9]

y7 = -0,5Х2+1.5,
х
[-1;1]

Скачано с www.znanio.ru

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • Разгруппировка строк в excel
  • Разгруппировка объектов в word
  • Разгруппировать столбец в excel
  • Разграничение прав доступа excel
  • Разгадай кроссворд в word

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии