Тема 4. Табличные прцессоры ЛЕКЦИЯ 6 ОКНО EXCEL Окно содержит множество различных эле

Работа добавлена на сайт samzan.net:

PAGE  12

                                        Тема 4. Табличные прцессоры

ЛЕКЦИЯ 6

ОКНО EXCEL

Окно содержит множество различных элементов. Некоторые из них присущи всем программам в среде Windows, остальные есть только в окне Excel.

Вся рабочая область окна занята чистым рабочим листом (или таблицей), разделенным на отдельные ячейки. Столбцы озаглавлены буквами, строки – цифрами.

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

Рабочий лист в виде отдельного окна со своим собственным заголовком (смотри рисунок, заголовок Книга1) называют РАБОЧЕЙ КНИГОЙ.

Рабочая книга в виде файла (формат - XLS)сохраняется на диске, содержит рабочие листы , листы диаграмм и все прочие типы листов.

На одном  рабочем листе расположено 256 столбцов и 65536 строк. Строки пронумерованы от 1 до 65536, столбцы названы буквами и комбинацией букв (A - Z, AA, AB и так далее). В ячейки рабочего листа можно вводить текст, числа, формулы.

АДРЕС ЯЧЕЙКИ

Адрес ячейки указывает её место в рабочем листе с помощью буквы столбца и номера строки. Например, B7 – это адрес ячейки, образованной пересечением столбца B со строкой 7.

Адреса ячеек можно использовать в формулах. Если в формуле содержится адрес ячейки (адрес в этом случае называют ссылкой на ячейку), то при вычислениях вместо него подставляется значение этой ячейки.

Чтобы создать ссылку на ячейку из другого рабочего листа, следует поместить перед ней название листа и восклицательный знак. Например, Лист2!D14.

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

Различают:

1.   относительный адрес ячейки - это ссылка на ячейку, которая подстраивается при копировании содержащейся в ячейке формулы (F7);
2.  абсолютный адрес ячейки  -  это ссылка на ячейку, которая не изменяется при копировании  содержащейся в ячейке формулы ($F$7);

3. смешанный адрес - это ссылка на ячейку, в которой только одна из компонент адреса абсолютна (F$7, $F7).

ПРИМЕРЫ.     

 1. Относительный адрес.

Записать формулу и значения в ячейках B2 и  B3:

2. Абсолютный адрес.

Записать формулу и значения в ячейках B2 и B3:

1.  Смешанный адрес (1).

Записать формулу и значения в ячейках B2 и B3: ______________________________________

1.  Смешанный адрес (2)

.

Записать формулу и значения в ячейках B2 и B3: ______________________________________

ФУНКЦИИ

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

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

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

В качестве аргумента можно использовать блок ячеек, который обозначается двоеточием. Например, =СУММ(A1:D3;F5;D5).

Результат_=___________

МАСТЕР ФУНКЦИЙ

Функцию можно ввести с помощью диалога называемого еще Мастером функций.

 

Мастер функций предоставляет две страницы (два шага) для ввода функции:

1.  первая страница – выбор категории функции и функции;

1.  вторая страница – задаются аргументы функции;

Выбранная функция суммирует все числа в интервале ячеек.

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

СУММ(число1;число2; ...)

Число1, число2, ...   - это от 1 до 30 аргументов, для которых требуется определить итог или сумму.

Учитываются числа, логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.

Если аргумент является массивом или ссылкой, то только числа учитываются в массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки. Примеры

СУММ(3; 2) равняется 5

СУММ("3"; 2; ИСТИНА) равняется 6, так как текстовые значения преобразуются в числа, а логическое значение ИСТИНА  преобразуется в число 1.

труктура   любой функции.

  Структура функции начинается с указания имени функции, затем вводится открывающая скобка, указываются аргументы, отделяющиеся точками с запятыми, а затем — закрывающая скобка. Если написание формулы начинается с функции, перед именем функции вводится знак равенства (=). 

КАТЕГОРИИ  ФУНКЦИЙ

 

Рассмотрим работу отдельных функций.

ПРИМЕР

1.  Заполнить приведённую таблицу.
2.  Определить фонд заработной платы по каждой должности (начальник, инженер, экономист, техник лаборант) и количество сотрудников каждой должности.
3.  Построить две гистограммы (по строкам и столбикам).

Ячейки столбцов A, B, E, G, H заполняются с клавиатуры произвольной информацией.

Рекомендации по заполнению ячеек:

•  A – любые 20 фамилий,
•  B - целые числа от 1 до 5 (числа повторяются),
•  E – целые числа от 1 до 40,
•  G – любой номер из семи цифр например, 111-22-33,
•  H – цифры 1 или 2.

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

Запустить программу Microsoft Excel, создать новую книгу, на Листе1 создать предложенную таблицу, заполнив указанные столбцы.

1. Заполнение столбца C

Функция  ВЫБОР Относится к категории  Ссылки и массивы.

Синтаксис

ВЫБОР(номер_индекса;значение1;значение2;…)

Номер_индекса  — это номер выбираемого аргумента-значения. Номер_индекса должен быть числом от 1 до 29, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 29. 

Значение1, значение2 ...  — это от 1 до 29 аргументов-значений, из которых ВЫБОР, используя номер_индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, именами, формулами, функциями или текстами.

На Листе2 создаём Справочник 1 для заполнения столбца Должность.

Затем в ячейку C2 Лист1 записываем функцию:

=Выбор(B2;Лист2!A$4;Лист2!A$5;Лист2!A$6;Лист2!A$7;Лист2!A$8)

ВОПРОС 1

Записать  функцию ВЫБОР для ячейки C3 (Лист1)

_=__________________________________________________

ВОПРОС 2

Заполнить все  ячейки столбца C на Листе1

2. Заполнение столбца D

Величина оклада зависит от занимаемой должности. Для определения оклада воспользуемся функцией СУММ(). 

Функция СУММ

Относится к категории  Математические.

Суммирует все числа в интервале ячеек.

Синтаксис

СУММ(число1;число2; ...)

Число1, число2, ...   - это от 1 до 30 аргументов, для которых требуется определить итог или сумму. Если аргумент является массивом или ссылкой, то только числа учитываются в массиве или ссылке.

Для вычисления оклада воспользуемся Справочником 1 (смотри Лист2), в столбце B которого представлены Тарифы.

Алгоритм для определения оклада:

1.  лаборант – 100
2.  техник     -  100+500
3.  экономист – 100+500+1000
4.  инженер   - 100+500+1000+2000
5.  начальник - 100+500+1000+2000+3000

В ячейку D2 записываем функцию:

=СУММ(Лист2!B$4:ВЫБОР(B2;Лист2!B$4;Лист2!B$5;Лист2!B$6;Лист2!B$7;Лист2!B$8))

Например, для Начальника:

в этой функции сначала вычисляется вложенная функция Выбор, которая возвращает ссылку на ячейку Лист2!B$8, а затем вычисляется функция СУММ(Лист2!B$4:Лист2!B$8)

ВОПРОС 3

Записать  функцию СУММ() для ячейки D3 (Лист1)

_=__________________________________________________

ВОПРОС 4

Заполнить все  ячейки столбца D на Листе1

1.  Заполнение столбца F

Величину премии вычисляем в зависимости от стажа работы.

На Листе2 создаём Справочник 2 для заполнения столбца Премия.

Для определения премии воспользуемся функцией Просмотр().

Относится к категории  Ссылки и массивы.

Возвращает значение из строки, из столбца или из массива. Функция ПРОСМОТР имеет две синтаксические формы: вектор и массив. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только  одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца

Синтаксис 1 

Векторная форма

ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

Искомое_значение   - это значение, которое ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

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

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

Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение.

В ячейку F2 записываем функцию:

=ПРОСМОТР(E2;Лист2!D$4:Лист2!D$12;Лист2!E$4:Лист2!E$12)

Например, для Начальника:

В ячейке E2 записан стаж 41 год. В  Просматриваемом_векторе (Лист2!D$4:Лист2!D$12), функция ПРОСМОТР не находит 41, поэтому подходящим считается наибольшее значение, то есть 40, а этому значению в Векторе_результатов (Лист2!E$4:Лист2!E$12) соответствует 7000.

ВОПРОС 5    Заполнить все  ячейки столбца F на Листе1

4. Заполнение столбца I

Для формирования номера Нового телефона воспользуемся функцией Сцепить().

Относится к категории  Текстовые.  Объединяет несколько текстовых строк в одну.

Синтаксис СЦЕПИТЬ (текст1;текст2;...)

Текст1, текст2, ...   - это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.

На Листе2 создаём Справочник 3 для заполнения столбца I.

В ячейку I2 записываем функцию:

=СЦЕПИТЬ(ВЫБОР(H2;Лист2!H$4;Лист2!H$5);" ";G2)

В ячейке I2  - новый телефон 8(499) 111-22-33.

5. Заполнение столбца J

Величина Налога зависит от оклада и МРОТ (минимальный размер оплаты труда).

Налог следует рассчитывать по следующим формулам:

                 0,                   если Оклад <=1000

                 0,12*Оклад,                   если 1000<Оклад<=2000

Налог =    0,12*Оклад+МРОТ,      если 2000<Оклад<=4000

                 0,12*Оклад+2*МРОТ,  если Оклад>4000

На Листе2 ячейке A4 присваиваем  имя МРОТ.

Для определения Налога воспользуемся функцией  ЕСЛИ().

Относится к категории Логические.

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис

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

Лог_выражение  — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

В ячейку J2 (Лист1) заносим функцию:

=ЕСЛИ(D2<=1000;0;ЕСЛИ(И(D2>1000;D2<=2000);0,12*D2;

ЕСЛИ(И(D2>2000;D2<=4000);0,12*D2+МРОТ;0,12*D2+2*МРОТ)))

ВОПРОС 6

Объяснить работу Логических функций И и ИЛИ

Ниже приведены заполненная таблица (Лист1) и справочная информация (Лист2).

1. Консенсусного лат
2. вариантов набора благ означает что для потребителя они взаимозаменяемы
3. ФИЗИЧЕСКАЯ КУЛЬТУРА И ЗДОРОВЬЕ на 2010-2011 уч
4. Сталинградская битва
5. ПРАВОВОЙ СТАТУС ЧЕЛОВЕКА И ГРАЖДАНИНА РЕСПУБЛИКИ БЕЛАРУСЬ
6. Компьютерные вирусы и антивирусы
7. Налог на прибыль организаций исчисляется для конкретного налогового периода; налогом облагается пр
8. ПАРАМЕТРИЧЕСКИЕ ПИП Пассивные чувствительные элементы Резистивные чувствительные элементы Омич
9. Тема проекту Значення води її охорона й економне використання Тривалість- 2 тижні.
10. судинними захворюваннями в основному періоді Ступінь індивідуально допустимого навантаження осіб що пер
11. тема обменивается с внешним миром
12. экономическими условиями формами собственности характером отношений между людьми социальной структурой
13. политические движения
14. тема России молода.
15. Тема- МАРКЕТИНГОВЫЕ ИССЛЕДОВАНИЯ 1 ПРИНЦИПЫ ФОРМИРОВАНИЯ И ИСПОЛЬЗОВАНИЯ МАРКЕТИНГОВОЙ ИНФОРМАЦИИ
16. Вестник хирургии член Союза писателей России почетный член многих отечественных и зарубежных научных общ.
17. Тема порядок и условия исполнения наказаний не связанных с изоляцией от общества
18. это целесообразная деятельность которая направлена на создание потребительской стоимости
19. 03 Хірургія А в т о р е ф е р а т дисертації на здобуття наукового ступеня кандидата медичних наук
20. І Реценз Н

Материалы собраны группой SamZan и находятся в свободном доступе