16376

ФОРМУЛЫ И ФУНКЦИИ В MS EXCEL

Лекция

Информатика, кибернетика и программирование

ФОРМУЛЫ И ФУНКЦИИ В MS EXCEL Все формулы в таблицах Excel начинаются со знаков = вводятся в активную ячейку и отображаются в строке формул. После ввода формулы в активной ячейке будет отображаться результат вычислений а

Русский

2013-06-20

923 KB

6 чел.

ФОРМУЛЫ И ФУНКЦИИ В MS EXCEL

Все формулы в таблицах Excel начинаются со знаков +, -, =, вводятся в активную ячейку и отображаются в строке формул. После ввода формулы в активной ячейке будет отображаться результат вычислений, а в строке формул – соответствующая формула. Для удобства сложные формул следует вводить непосредственно в строке формул. Например:

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

Операции над данными в MS Excel.

Операции делятся на арифметические, текстовые, операции сравнения.

Арифметические операции: + (сложение), - (вычитание), * (умножение), / (деление), ^ (возведение в степень), % (взятие процента – умножение на 100 и добавление значка %).

Текстовая операция: & (конкатенация, т.е. объединение строк).

Операции сравнения: > (больше), <(меньше), = (равно), >= (больше или равно), <= (меньше или равно), <> (не равно).

Данные, используемые в формулах

В формулах используются числовые данные, к которым относятся : целые числа (5, -13, 290 и т.п.), вещественные числа – т.е. числа с дробной частью, как правило, отделяемой от целой части запятой (12,55  –13,248) , т.е. представляемые в форме с плавающей точкой, либо (12,45Е+02   -3,06е-3), т.е. представляемые в экспоненциальной форме; даты, представляемые в одном из форматов, используемом в MS Excel, (например: 12 февраля 2005 г., 12/02/05, 12.02.05, 12.02.2005 и т.п.); время (12:15, 12:15:02 и.т.п.) . Используемые форматы можно просмотреть по команде Формат/Формат ячеек/Вкладка Числа.

Текстовые данные заключаются в двойные кавычки, (например, “Ф.И.О.”, “Менеджер” и т.п.).

Ссылки на ячейки или диапазоны ячеек

В формулах могут использоваться ссылки на одну ячейку или на группу как смежных, так и несмежных ячеек. Ссылки могут быть относительными (В1, С10), абсолютными($B$1, $C$10), и смешанными ($B1,B$1,$C10,C$10).

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

A

B

C

D

в B2 вводим,

В B2 вводим

A

B

C

D

1

1

2

3

формулу =A1+B3

формулу =$A$1+B3,

1

1

2

3

2

4

7

2

протягиваем

протягиваем ее в

2

4

7

1

3

5

6

2

ее в ячейку С2 и D3

ячейку С2 и D3 и

3

5

6

1

4

и получаем

получаем результат

4

результат (слева)

(справа)

А

B

C

D

В B2 вводим

В B2 вводим

A

B

C

D

1

1

2

3

формулу =$A1+B3,

формулу =A$1+B3,

1

2

3

2

4

7

1

протягиваем ее в

протягиваем ее в

1

4

7

2

3

5

6

4

ячейку С2 и D3 и

ячейку С2 и D3 и

2

5

6

3

4

получаем результат

получаем результат

3

(слева)

(справа)

4

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

Таким образом,

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

Ссылка на диапазон ячеек формируется с помощью ссылки на левую верхнюю и правую нижнюю ячейки, между которыми ставится двоеточие, например: A1:E5 (диапазон ячеек с относительными ссылками), $A$1:$E$5 (диапазон ячеек с абсолютными ссылками), A$1:E$1 (диапазон ячеек со смешанными ссылками).

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

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

Лист1!$A$5. В том случае, если в названии листа имеется пробел, оно заключается в апострофы, например ‘Динамика выплат’!$A$5.

Ссылки на ячейки в других рабочих книгах формируются указанием названия рабочей книги с расширением, заключенным в квадратные скобки, за которым следует ссылка на ячейки рабочего листа, например: [Книга1.xls]Лист1!$A$5.

Если в названии листа имеются пробелы, то название книги и листа заключаются в апострофы, например ‘[Книга1.xls]Динамика выплат!’$A$5.

Функции можно вставлять в формулы двумя способами:

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

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

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

И в том, и в другом случае следует найти нужную функцию, что можно сделать, нажав кнопку , либо, вызвав команду Вставка/Функция. При этом появляется диалоговое окно Мастер функций. При нажатии ОК появляется окно функции (например, СУММ) в виде:

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

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

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

СЕГОДНЯ( ) (возвращает текущую дату);

ПИ( ) (возвращает число );

КОРЕНЬ( число) (возвращает квадратный корень Числа);

СУММ(Число1;Число2;…) (возвращает сумму аргументов);

СРЗНАЧ(Число1;Число2;…) (возвращает среднее арифметическое значение чисел в списке аргументов);

СЧЕТ(Значение1;Значение2;….) (возвращает количество чисел в списке аргументов);

СЧЕТЗ(Значение1;Значение2;…) (возвращает число аргументов в списке, исключая пустые ячейки);

СЧИТАТЬПУСТОТЫ(Диапазон) (подсчитывает количество пустых ячеек в Диапазоне);

И(логическое значение1;логическое значение2;….) (возвращает значение ИСТИНА, если истинны все логические значения и ЛОЖЬ, если ложно хотя бы одно из них);

ИЛИ(логическое значение1;логическое значение2;….) (возвращает значение ИСТИНА, если истинно хотя бы одно из логических значений и ЛОЖЬ, если ложны все логические значения);

МАКС(Число1;Число2;…) (возвращает максимальное число из перечисленных аргументов)

МИН(Число1;Число2;…) (возвращает минимальное число из перечисленных аргументов)

ЕСЛИ(Условие;Значение_если_истина;Значение_если_ложно) (Определяет значение в зависимости от истинности или ложности условия);

СЧЕТЕСЛИ(Диапазон;Условие) (возвращает количество ячеек из диапазона, удовлетворяющих условию);

СУММЕСЛИ(Диапазон;Условие;Диапазон_суммирования) (возвращает сумму чисел в диапазоне суммирования, если соответствующие ячейки из диапазона удовлетворяют условию);

ТЕНДЕНЦИЯ(известные значения y; известные значения x; новое значение x) (возвращает значение у, соответствующее новому значению х и найденное в соответствии с линейной моделью, построенной по методу наименьших квадратов по известным значениям х и у);

РОСТ(известные значения y; известные значения x; новое значение x) (возвращает значение у, соответствующее новому значению х и найденное в соответствии с экспоненциальной моделью, построенной  по известным значениям х и у).

Рассмотрим пример использования формул и функций.

Допустим, что задан список сотрудников некоторой фирмы в виде таблицы MS Excel (Столбцы ФИО, Должность, Отдел, Дата поступления) и исходные параметры (Минимальный оклад по фирме, Подоходный налог, Пенсионные отчисления, Обменный курс доллара USA). Необходимо, используя формулы и функции рассчитать и заполнить соответствующие столбцы :

  •  Оклад, исходя из следующего: Оклад директора – 300% , Гл.инженера и Гл.бухгалтера – 200% , остальным 100% от минимального оклада по фирме
  •  Стаж на текущую дату
  •  Процент надбавки за стаж, исходя из следующего: при стаже, большем или равном 15 лет – 30%, большем или равном 10 лет – 20%, большем или равном 5 лет – 5% от минимального оклада по фирме.
  •  К выдаче в рублях
  •  К выдаче в долларах 
  •  Среднюю зарплату по отделам
  •  Суммарные выплаты в рублях и в долларах

Заполним таблицу, вводя в соответствующие ячейки строки 3 формулы:

  •  Для расчета оклада:
    =ЕСЛИ(C3="Директор";300%*$B$10;ЕСЛИ(ИЛИ(C3="Гл.инженер";C3="Гл.бухгалтер");200%*$B$10;100%*$B$10))
  •  Для расчета стажа:
    =(СЕГОДНЯ()-D3)/365
  •  Для расчета процента надбавки за стаж:
    =ЕСЛИ(F3>=15;30%;ЕСЛИ(F3>=10;20%;ЕСЛИ(F3>=5;5%;0%)))
  •  Для расчета зарплаты (К выдаче в рублях):
    =E3-($B$11+$B$12)*E3+G3*$B$10
  •  Для расчета зарплаты (К выдаче в долларах):
    =H3/$B$13
  •  Для вычисления итоговой суммы в рублях:
    =СУММ(H3:H8)
  •  Для вычисления средней зарплаты сотрудников отдела Маркетинг:
    =СУММЕСЛИ(B$3:B$8;"Маркетинг";H$3:H$8)/СЧЁТЕСЛИ(B$3:B$8;"Маркетинг")

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

 

4

PAGE  5


 

А также другие работы, которые могут Вас заинтересовать

84279. Спиртовое брожение. Химизм, условия проведения процесса. Возбудители. Практическое использование спиртового брожения 34.17 KB
  Практическое использование спиртового брожения Спиртовое брожение микробиологический процесс превращения углеводов в спирт и углекислый газ. Суммарное уравнение реакции: С6 H12 O6 → 2 СНзCH2 ОН 2 СО2 Е глюкоза этиловый спирт Как и любое брожение это сложный многоступенчатый процесс см. Дрожжи верхового брожения вызывают бурное и быстрое брожение при температуре 2028 С.
84280. Химизм процесса. Характеристика молочнокислых бактерий. Практическое значение молочнокислого брожения 33.66 KB
  Суммарное уравнение процесса имеет вид: С6H12О6 СНзСНОНСООН СООНСН2СН2СООН СНзСООН глюкоза молочная кислота янтарная кислота уксусная кислота СНзСН2ОН C02Н2 Е этиловый спирт К гетероферментативным молочнокислым бактериям относятся бактерии рода Streptococcus: Streptococcus dicetilctis Streptococcus cetoinicus; бактерии рода Lctobcillus: Lctobcillus brevis Lctobcillus helveticus а также бактерии рода Leuconostoc: Leuconostoc mesenteroides Leuconostoc cremoris. Характеристика молочнокислых бактерий Все молочнокислые бактерии...
84281. Пропионовокислое брожение. Химизм процесса, возбудители. Практическое использование пропионовокислого брожения 30.49 KB
  Практическое использование пропионовокислого брожения Пропионовокислое брожение вызывается пропионовокислыми бактериями относящимися к роду Propionibcterium. Химизм пропионовокислого брожения: ЗС6H12О6 → 4СНзCH2СООН 2СНзСООН 2CO2 2H2O Е глюкоза пропионовая уксусная кислота кислота Пропионовокислые бактерии небольшие неподвижные грамположительные палочки не образующие спор факультативные анаэробы. Практическое применение пропионовокислого брожения Пропионовокислое брожение используется в сыроделии.
84282. Маслянокислое брожение. Химизм процесса. Возбудители. Практическое использование и роль в процессах порчи пищевых продуктов 32.61 KB
  Эти бактерии могут сбраживать многие углеводы, в т.ч. (крахмал, гликоген, пектиновые вещества, целлюлозу), спирты (этиловый, маннит, глицерин) и аминокислоты. По характеру используемых субстратов маслянокислые бактерии делятся на две группы: сахаролитические клостридии, которые сбраживают в основном углеводы
84283. Уксуснокислое брожение. Химизм процесса. Возбудители. Практическое использование и роль в процессах порчи пищевых продуктов 31.83 KB
  Возбудителями уксуснокислого брожения являются уксуснокислые бактерии относящиеся к двум родам: Gluconobcter и cetobcter. Бактерии кислотоустойчивы оптимальное значение рН для развития 5463. С другой стороны уксуснокислые бактерии являются вредителями спиртового пивоваренного консервного производств виноделия производства безалкогольных напитков.
84284. Окисление жиров и высших жирных кислот микроорганизмами. Микроорганизмы - возбудители порчи жиров 32.33 KB
  Микроорганизмы возбудители порчи жиров Жиры представляют собой сложные эфиры глицерина и высших жирных кислот. Практическое значение процесса Процесс разложения жиров отмерших животных и растений происходит постоянно и имеет большое значение в круговороте веществ в природе. С другой стороны в пищевой промышленности микроорганизмы окисляющие жиры приносят вред вызывая порчу пищевых жиров и жира содержащихся в различных пищевых продуктах.
84285. Гнилостные процессы. Понятие об аэробном и анаэробном гниении. Возбудители. Роль гнилостных процессов в природе, в пищевой промышленности 33.82 KB
  Белки высокомолекулярные соединения поэтому вначале они подвергаются внеклеточному расщеплению протеолитическими ферментами микроорганизмов которые являются экзоферментами. Конечными продуктами аэробного гниения являются кроме аммиака диоксид углерода сероводород и меркаптаны обладающие запахом тухлых яиц. Конечными продуктами анаэробного гниения являются продукты декарбоксилирования аминокислот отнятие карбоксильной группы с образованием дурно пахнущих веществ: индола акатола фенола крезола диаминов их производные являются...
84286. Характеристика пищевых заболеваний. Отличия пищевых инфекций от пищевых отравлений 27.71 KB
  Отличия пищевых инфекций от пищевых отравлений Пищевые алиментарные заболевания заболевания причиной которых служит пища инфицированная токсигенными микроорганизмами или токсинами микробов рис.1 Сравнительная характеристика пищевых заболеваний № Пищевые инфекции Пищевые отравления 1. Возбудители в пищевых продуктах не размножаются но могут длительное время сохраняться.
84287. Патогенные и условно-патогенные микроорганизмы. Их основные свойства. Химический состав и свойства микробных токсинов 34.57 KB
  Химический состав и свойства микробных токсинов Возбудителями пищевых инфекций являются патогенные микроорганизмы к основным свойствам которых относятся: Патогенность потенциальная способность определенного вида микробов приживаться в макроорганизме размножаться и вызывать определенное заболевание. Все патогенные микроорганизмы относятся к хемоорганогетеротрофам которые в качестве источника углерода и азота используют органические соединения из живых клеток паразиты. Возбудителями пищевых отравлений являются условнопатогенные...