Текстовые функции. Функция ВПР

Текстовые функции

 

► ПРОПИСН (текст)

Делает все буквы в тексте прописными.

 

Текст — текст, преобразуемый в верхний регистр. Текст может быть ссылкой на текст или текстовой строкой.

► СТРОЧН (текст)

Преобразует знаки в текстовой строке из верхнего регистра в нижний.

 

Текст — текст, преобразуемый в нижний регистр. Функция СТРОЧН не меняет знаков, которые не являются буквами.

► ПРОПНАЧ (текст)

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

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

 

► ЛЕВСИМВ (текст; количество_знаков)

возвращает указанное число знаков с начала текстовой строки.

 

Текст — текстовая строка, содержащая извлекаемые знаки.

Количество_знаков — количество знаков, извлекаемых функцией ЛЕВСИМВ.

· Количество_знаков должно быть больше либо равно нулю.

· Если количество_знаков больше длины текста, то функция ЛЕВСИМВ возвращает весь текст.

· Если количество_знаков опущено, то предполагается, что оно равно 1.

► ПРАВСИМВ (текст; количество_знаков)

возвращает указанное число последних знаков текстовой строки.

 

Текст — текстовая строка, содержащая извлекаемые знаки.

Количество_знаков — количество знаков, извлекаемых функцией ПРАВСИМВ.

· Количество_знаков должно быть больше либо равно нулю.

· Если количество_знаков больше длины текста, то функция ПРАВСИМВ возвращает весь текст.

· Если количество_знаков опущено, то предполагается, что оно равно 1.

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

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

 

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

· Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор «&».

 

► ПСТР (текст; начальная_позиция; число_знаков)

возвращает указанное число знаков из текстовой строки, начиная с указанной позиции.

 

Текст — текстовая строка, содержащая извлекаемые знаки.

Начальная_позиция — позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Число_знаков указывает, сколько знаков требуется вернуть.

• Если начальная_позиция больше, чем длина текста, то функция ПСТР возвращает строку "" (пустой текст).
• Если начальная_позиция меньше, чем длина текста, но начальная_позиция плюс количество_знаков превышают длину текста, то функция ПСТР возвращает знаки вплоть до конца текста.

Примеры текстовых функций

	A	B	C
	Комаров	Михаил	Петрович
	Экономический	эф
	МГУ
		ЭФ			=ПРОПИСН(B2)
		мгу			=СТРОЧН(A3)
		Эф			=ПРОПНАЧ(В2)
		Ми			=ЛЕВСИМВ(В1;2)
		вич			=ПРАВСИМВ(С1;3)
	МГУЭкономический				=СЦЕПИТЬ(А3;A2)
	МГУ Экономический				=СЦЕПИТЬ(А3;" ";A2)
		М			=ПСТР(А3;1;1)
		Г			=ПСТР(А3;2;1)
		У			=ПСТР(А3;3;1)
	Комаров М.П.	=СЦЕПИТЬ(A1;" "ЛЕВСИМВ(B1);"."; ЛЕВСИМВ(C1);".")

 

=A1 & " " & ЛЕВСИМВ(B1) & "." & ЛЕВСИМВ(C1) &"."

2. Функция ВПР

ВПР (искомое_значение; таблица; №стл; интервальный_просмотр)

Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.

Искомое_значение — это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Таблица — таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список.

· Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.

· Значения в первом столбце аргумента «таблица» могут быть текстовыми строками, числами или логическими значениями.

· Текстовые строки сравниваются без учета регистра букв.

№стл — это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «№стл» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «№стл» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «№стл» больше, чем количество столбцов в аргументе «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!.

Интервальный_просмотр — это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

 

 

= ВПР (Искомое значение; таблица; №стл; ЛОЖЬ)

 

А

В

С

D

E

F

G

H

I

J

K

L

M

ПРИМЕРЫ:

 

	А	В	С	D	E	F
		Фамилия	Отдел	Оклад	Отпуск
		Иванов			Март
		Смирнов			Январь
		Семенов			Сентябрь
		Абрамов			Май

 

 

Пример 1:

Вывести оклад Семенова.

= ВПР (“Семенов”; $B$3:$E$6; 3; ЛОЖЬ) → 5400

 

 

Пример 2:

Является ли оклад Семенова максимальным?

= ЕСЛИ (ВПР (“Семенов”; $B$3:$E$6; 3; ЛОЖЬ) = МАКС($D$3:$D$6); " ДА "; " НЕТ ") → НЕТ

 

 

Пример 3:

Определить фамилию работника, имеющего максимальный оклад.

 

Для решения этой задачи необходимо перестроить таблицу: поменять местами столбцы Оклад и Фамилия

 

	А	В	С	D	E	F
		Оклад	Отдел	Фамилия	Отпуск
				Иванов	Март
				Смирнов	Январь
				Семенов	Сентябрь
				Абрамов	Май

 

= ВПР (Макс($B$3:$B$6); $B$3:$E$6; 3; ЛОЖЬ) → Смирнов

Вопрос: Для чего надо было менять местами столбцы Оклад и Фамилия?

САМОСТОЯТЕЛЬНАЯ. Функция ВПР и текстовые функции.

 

	A	B	C	D	E	F	G	H
			Факул.	Фамилия	Имя	Отчество	Должн.
			ММ	ДЕНИСОВ	Николай	Александрович
			ММ	ДОВОЛЬНОВ	Артем	Андреевич
			ВМК	ЗАЦЕПИН	Андрей	Сергеевич	ЗУР
			ММ	ЗЮЗИН	Алексей	Юрьевич	Декан
			ВМК	КАПАЛИН	Иван	Владимирович
			ММ	КИРЕЕВ	Алексей	Андреевич	ЗУР
			ММ	КОНДРАТЮК	Антон	Алексеевич
			ВМК	КОРЖИКОВ	Александр	Михайлович	Декан
			ММ	КОРНАЧЕВ	Иван	Андреевич

 

Используя функцию ВПР и необходимые текстовые функции, получите

следующую таблицу:

 

	A	B	C	D	E	F	G
…
				Факультет	Декан	ЗУР
				ММ	ЗЮЗИН А.Ю	КИРЕЕВ А.А
				ВМК	КОРЖИКОВ А.М	ЗАЦЕПИН А.С

 

Решение:

=СЦЕПИТЬ($C3; $G3) =СЦЕПИТЬ($D3; " "; ЛЕВСИМВ($E3); "."; ЛЕВСИМВ($E3))

	A	B	C	D	E	F	G
			Факул.	Фамилия	Имя	Отчество	Должн.
	ММ	ДЕНИСОВ Н.А	ММ	ДЕНИСОВ	Николай	Александрович
	ММ	ДОВОЛЬНОВ А.А	ММ	ДОВОЛЬНОВ	Артем	Андреевич
	ВМКНУЧ	ЗАЦЕПИН А.С	ВМК	ЗАЦЕПИН	Андрей	Сергеевич	НУЧ
	ММДекан	ЗЮЗИН А.Ю	ММ	ЗЮЗИН	Алексей	Юрьевич	Декан
	ВМК	КАПАЛИН И.В	ВМК	КАПАЛИН	Иван	Владимирович
	ММНУЧ	КИРЕЕВ А.А	ММ	КИРЕЕВ	Алексей	Андреевич	НУЧ
	ММ	КОНДРАТЮК А.А	ММ	КОНДРАТЮК	Антон	Алексеевич
	ВМКДекан	КОРЖИКОВ А.М	ВМК	КОРЖИКОВ	Александр	Михайлович	Декан
	ММ	КОРНАЧЕВ И.А	ММ	КОРНАЧЕВ	Иван	Андреевич

 

=ВПР (СЦЕПИТЬ($D15;$E$14); $A$3:$B$11; 2; ЛОЖЬ)

	A	B	C	D	E	F	G
…
				Факультет	Декан	НУЧ
				ММ	ЗЮЗИН А.Ю	КИРЕЕВ А.А
				ВМК	КОРЖИКОВ А.М	ЗАЦЕПИН А.С

 

=ВПР (СЦЕПИТЬ($D15;$F$14); $A$3:$B$11; 2; ЛОЖЬ)

Самостоятельная.

Подсчитать количество студентов в каждой группе на каждом факультете. Исходные данные на Листе 1. Отчет в указанной форме представить на Листе 2

 

	A	B	C	D			A	B	C	D	Е
		Фамилия	Фак.	Гр.				Фак.	Гр.	Колич.	Общ.
		АЛЕКСЕЕВ А.В	ВМК					ВМК
		АРАКЕЛЯН А.В	ММ
		АРСЕЕВ А.П	ММ
		АБРАМОВ И.Х	ЭФ					ММ
		БАКУЛИН А.В	ВМК
		БАРАНОВ А.Д	ЭФ
		БОРИСОВ С.А	ВМК					ЭФ
		БРЫКОВ А.К	ММ
		БУРХИНОВ М.В	ММ
		ВОЕВОДИН В.В	ЭФ
…		……………….	……..	…
		ДЕНИСОВ Н.А	ВМК
Лист 1		Лист 2

 

Решение

	A	B	C	D	E
		Фамилия	Фак.	Гр.
		АЛЕКСЕЕВ А.В	ВМК		ВМК2	=СЦЕПИТЬ($C3; $D2)
		АРАКЕЛЯН А.В	ММ		ММ3
		АРСЕЕВ А.П	ММ		ММ2
		АБРАМОВ И.Х	ЭФ		ЭФ1
		БАКУЛИН А.В	ВМК		ВМК2
…		……………….	……..	…
		ДЕНИСОВ Н.А	ВМК		ВМК3

	A	B	C	D	Е
		Фак.	Гр.	Колич.	Общ.
		ВМК			39	(E3) =СУММ($D3:$D5)
			11	(D5) =СчетЕсли(Лист1!$E$3:$E$77; B3&C5)
		ММ
		ЭФ
Лист 2

 

Условное форматирование

1. Выберите ячейку, для которой требуется добавить, изменить или удалить условное форматирование (Условный формат. Формат (например, узор ячейки или цвет шрифта), который Microsoft Excel автоматически применяет к ячейке, если выполняется указанное условие.).
2. В меню Формат выберите команду Условное форматирование.
3. Выполните одно из следующих действий.

Чтобы в качестве условия форматирования использовать значения выделенных ячеек, выберите параметр значение, выберите операцию сравнения, а затем введите заданное (Константа. Постоянное (не вычисляемое) значение. Например, число 210 и текст «Квартальная премия» являются константами. Формула и результат вычисления формулы константами не являются.) значение или формулу. Перед формулой нужно поставить знак равенства (=).

Для использования формулы в качестве критерия форматирования (для оценки данных или условий, отличных от значений выделенных ячеек), выберите параметр Формула, а затем введите формулу, принимающую логическое значение ИСТИНА или ЛОЖЬ.

1. Выберите команду Формат.
2. Выберите тип форматирования, который требуется применить, когда значение ячейки отвечает условию или формула возвращает значение ИСТИНА.

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

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

Пример условного форматирования для значения

 

 

 

 

	A	B	C	D	E	меньше значение F
						формат
						больше значение
						формат

равно

формат

значение

 

Пример условного форматирования для формулы

 

Условное форматирование

	A	B	C	D	E
		Фамилия	Отдел	Оклад	формула =$D3>8500
		Алхазов
		Анисимов			формат
		Артюхов
		Бабушкин
		Бадосов
		Барков			формула =$D3<6000
		Беженар
					формат

 

Защита информации

 

Внимание!

 

Пройдите путь: Формат ► Ячейки… ► Защита (закладка)

 

На панели видно, что окно «защищаемая ячейка», активировано. Это означает, что при защите листа, данная ячейка будет защищена от изменений.

Если снять признак «защиты» то, при защите листа, содержимое данной ячейки может редактироваться.

 

Защита листа.

 

Установить защиту листа

Сервис ► Защита ► Защитить лист (Пароль вводить необязательно)

 

При установке защиты листа блокируется доступ ко всем ячейкам данного листа (на запись, удаление, изменение…..) кроме ячеек у которых снят признак «защищаемая ячейка» (Формат ► Ячейки… ► Защита)

Снять защиту листа

Сервис ► Защита ► Снять защиту листа (Если необходимо, введите Пароль)

 

Аналогично и с возможностью скрывать формулы. Формат ► Ячейки… ► Защита

 

ЗАДАНИЕ:

 

1. Защитить лист с таблицей.
2. Для полей Группа, Пр1, Пр2, Пр3 –создать выпадающий список и разрешить редактирование.
3. Формулы в поле Ср.балл сделать невидимыми.
4. Красным фоном выделить фамилию студента, средний балл которого «5» (условное форматирование)
5. Белым цветом на черном фоне выделить двойки по предметам. (условное форматирование)

 

 

Фамилия	Группа	Пр. 1	Пр 2	Пр. 3	Ср. балл
Абрамов					4,33
Борисов					3,67
Воробьев					4,00
Гришин					4,00
Игнатьев					3,33
Корнетов					5,00
Михайлов					4,33
Павлов					4,33
Рябов					4,67
Смирнов					4,00
Урусов					5,00
Яшин					4,00

Самостоятельные работы.

 

Самостоятельная 1.

 

Задание:

Распределить общий ДОХОД предприятия среди сотрудников в соответствии с коэффициентом участия (Таблица1.) Определить сумму выплат за вычетом Налога. Результат оформить в соответствии с формой Раздаточной ведомости.

Используемые формулы и обозначения:

n- количество сотрудников.

k_i - коэффициент участия i -го сотрудника.

- сумма коэффициентов участия.

S - доход предприятия

z% - процент налога.

m=S/C - цена одной единицы.

X=m*k_i - сумма i -му сотруднику.

H=X*z%` - налог.

Y=X-H -к выплате i -му сотруднику с учетом налога.

Определить общую сумму выплат, сумму налога, и сумму к выплате.

 

Таблица к Самостоятельной 1.

 

A

B

C

D

E

F

G

H

I

J

K

L

ДОХОД (S)

Налог (z%)

13%

Таблица 1

РАЗДАТОЧНАЯ ВЕДОМОСТЬ

№

Фамилия

Имя

Отчество

Коэф. (ki)

№

Фамилия

Сумма (X)

Налог (H)

К выплате (Y)

Бакулин

Антон

Викторович

БАКУЛИН А.В.

1531,25

199,06

1332,19

Баранов

Александр

Дмитриевич

БАРАНОВ А.Д.

2406,25

312,81

2093,44

Борисов

Сергей

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

БОРИСОВ С.А.

1750,00

227,50

1522,50

Брыков

Антон

Константинович

БРЫКОВ А.К.

1203,13

156,41

1046,72

Бурхинов

Михаил

Викторович

БУРХИНОВ М.В.

984,38

127,97

856,41

Воеводин

Вадим

Владимирович

ВОЕВОДИН В.В.

1750,00

227,50

1522,50

Газизов

Тимур

Наилевич

ГАЗИЗОВ Т.Н.

1421,88

184,84

1237,03

Гацко

Виталий

Геннадьевич

ГАЦКО В.Г.

1093,75

142,19

951,56

Глотов

Артем

Владимирович

ГЛОТОВ А.В.

1859,38

241,72

1617,66

ИТОГО

14000,00

1820,00

12180,00

Рисунок

Самостоятельная 2.

Решить уравнение.

 

 

 

	A	B	C	D	E	F	G	H
			X =			у =	2,137878211

 

Самостоятельная 3.

Задание:

Определить объект в соответствии с его характеристиками.

Объект, должен определятся если: число совпадений в характеристике больше трех, в любой комбинации, и нет повторений, иначе "??????????"

 

Объект	Характеристика объекта
Птица	крылья	перья	хвост	клюв	глаза
Рыба	хвост	плавник	глаза	жабры	чешуя
Лошадь	копыта	глаза	хвост	уши	грива
Тигр	клыки	хвост	глаза	шерсть	лапы

 

Объект	Характеристика объекта
Тигр	лапы	хвост	шерсть	глаза	жабры

 

 

Самостоятельная 4.

Задание (Таблица 1):

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

 

Таблица 1 Таблица 2

Курс на момент окончания срока         Курс P     Курс $ 29,75   Курс? 37,42       Вкладчик Сумма в конце срока Сумма в руб. Романов (Р) 1040,60 1040,60 Синицын ($) 36,04 1072,20 Гарин (?) 28,66 1072,30

	Курс на момент вклада
	Курс P
	Курс $	28,35
	Курс?	35,42
Рубли	Вкладчик	Валюта	Процент годовых
	Романов (Р)	1000,00	12,00
	Синицын ($)	35,27	6,50
	Гарин (?)	28,23	4,50

Определить (Таблица 2):

Какой вид вклада будет наилучшим в конце срока, если валютный курс изменяется.

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

Самостоятельная 5.

Решить систему линейных уравнений с помощью правила Крамера.

 

					Корни уравнения
	x+3y-7z=68		x=	21,6094
	x+5y+4z=25		y=	4,45313
	2x+4y+7z=28		z=	-4,7188

 

Правило Крамера

 

 

		-7
			Δ=
		-7
			Δx=
		-7
			Δy=
			Δz=	-302

 

Определитель системы уравнений

 

 

Дополнительный определитель (х)

 

 

Дополнительный определитель (y)

 

Дополнительный определитель (z)

 

Самостоятельная 6.

В Таблице 1 представлены сроки наступления некоторых платежей.

Определить фамилии клиентов просроч