19. Функция объединения. Функция =СЦЕПИТЬ(Текст1;Текст2;...)
Данные об осужденных
| А | В | С | D | Е | |
| Фамилия | Имя | Статья | Кодекс | Срок (лет) | |
| Иванов | Альберт | УК РФ | |||
В ячейку D3 ввести время 10:00, в ячейку ЕЗ – 20:00.
В ячейку B3:
=СЦЕПИТЬ("Посетите нашу адвокатскую контору по рабочим дням с";ТЕКСТ(D3;"ЧЧ:ММ");"до";ТЕКСТ(E3;"ЧЧ:ММ"))
Задание 21. Логические функции
Эта группа встроенных функций используется при решении экономических задач, где в зависимости от тех или иных условий решение должно пойти по одной или другой ветви. В качестве условий может быть равенство или неравенство сравниваемых величин, причем возможно несколько сравнений для выбора хода решения. В табл. 12 приведены функции этой категории с поясняющими примерами.
Таблица 12
Логические функции
| Название функции и ее синтаксис | Пример |
| ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) | В ячейках А1:А4 находятся числа 23, 45, 33, 87. Необходимо получить результаты в ячейках диапазона В1:В4 при таком условии: если. в ячейке столбца А число меньше 50, то умножить его на 10, если равно или больше 50, то умножить на 20. Для этого введем в ячейку В1 функцию = ЕСЛИ(А1>=50;А1*20;А1*10) и скопируем функцию в ячейки В2:В4 |
| И(логическое_значение1;логическое_значение2) Обычно эта функция работает как вложенная в ЕСЛИ, например, ЕСЛИ(И(логическое_значение1;логическое_значение2); значение_если_истина;значениеесли_ложь) | Для каждой ячейки из диапазона Al:A4 предыдущего примера проверить условие: если значение ячейки {Ai} больше 40 и одновременно меньше 100, то умножить его на 5, в противном случае оставить значение ячейки {Ai} неизменным. Результаты получить в ячейках С1:С4 Для решения введем в ячейку С1 функцию: =ЕСЛИ(И(Al>40;A1<100);Al*5;A1) и скопируем функцию в ячейки С2:С4 |
| ИЛИ (логическое_значение1;логическое_значение2) Обычно эта функция работает как вложенная в ЕСЛИ, например ЕСЛИ (ИЛИ(логическое_значение1;логическое_значение2);значение_если_истина;значение_если_ложь) | Для каждой ячейки из диапазона Al:A4 предыдущего примера проверить условие: если значение ячейки {Ai} меньше 40 или больше 50, то умножить его на 5, в противном случае оставить значение ячейки {Ai} неизменным. Результаты получить в ячейках С1:С4 Для решения введем в ячейку С1 функцию: =ЕСЛИ(ИЛИ(А1<40;А1>50);Al*5;A1) и скопируем функцию в ячейки С2:С4 |
1. На листе сформируйте таблицу из пяти столбцов следующего вида[2] (табл. 13).
Таблица 13
Характеристики изделий
| A | B | С | D | E | |
| Марка изделия | Цвет корпуса | Год выпуска | Цена, руб. | Решение | |
| Рубин | Черный | Нет | |||
| Салют | Красный | Нет | |||
| Темп | Серый | Да | |||
| Ладога | Серый | Нет | |||
| Радуга | Черный | Нет |
2. Разработайте функцию для отбора изделия (выдавать Да в столбце Решение) при совпадении характеристик (цвет – серый, год выпуска – 1999, цена – не ниже 4800 и не выше 5180 руб.).
3. Результат сохраните в этой же книге.
Ответ:
=ЕСЛИ(И(B3="Серый";C3=1999;D3<=5190;D3>=4500);"ДА";"НЕТ")
Задание 22. Простая функция ЕСЛИ
Формат записи: =ЕСЛИ(условие;выражениеВ;выражениеС)
Ряд работников имеет задолженность по потребительскому кредиту, которая отражена в диапазоне С3:С7 (рис. 56). Необходимо найти в списке таких работников и удержать с них в счет погашения кредита 10% от начисленной им суммы.
| A | B | C | D | E | |
| Ф.И.О | Начислено, тыс. руб. | Задолженность по видам кредита | Удержано, тыс. руб. | ||
| Потребительский кредит | Жилищное строительство | ||||
| Иванов | =ЕСЛИ(C3>0;B3*0,1;" ") | ||||
| Петров | =ЕСЛИ(C4>0;B4*0,1;" ") | ||||
| Кузьмин | =ЕСЛИ(C5>0;B5*0,1;" ") | ||||
| Сухов | =ЕСЛИ(C6>0;B6*0,1;" ") | ||||
| Рысьев | =ЕСЛИ(C7>0;B7*0,1;" ") |
Рис. 56. Пример простой логической функции ЕСЛИ
Для нашего примера логическая функция будет иметь следующий вид: =ЕСЛИ(C3>0;B3*0,1;" ").
Данная логическая функция означает следующее: если задолженность по потребительскому кредиту больше нуля, то необходимо удержать 10% с начисленной суммы, в противном случае необходимо вывести пробелы.
Запишем ее в ячейку Е3, а затем скопируем в ячейки Е4:Е7.
По диапазону С3:С7 отыскиваются работники, у которых есть задолженность по потребительскому кредиту, а в диапазоне Е3:Е7 указывается размер удержанной суммы. В случаях когда задолженности по потребительскому кредиту нет, напротив фамилии соответствующих работников в диапазоне Е3:Е7 выводятся пробелы.
В результате в диапазоне Е3:Е7 (рис. 57) получим размер удержанной суммы с работников, у которых имеются задолженности по потребительскому кредиту.
| A | B | C | D | E | |
| Ф.И.О | Начислено, тыс. руб. | Задолженность по видам кредита | Удержано, тыс. руб. | ||
| Потребительский кредит | Жилищное строительство | ||||
| Иванов | |||||
| Петров | |||||
| Кузьмин | |||||
| Сухов | |||||
| Рысьев |
Рис. 57. Результат выполнения простой логической функции ЕСЛИ
Задание 23.
Подсчитайте количество отличных, хороших и т.д. оценок на основании зачетной ведомости, представленной в таблице, представленной ниже.
| A | B | C | D | E | F | G | H | I | |
| № п/п | Ф.И.О | № зачет. книжки | Оценка | Количество | Кол. | Кол. | Кол. | Неявка | |
| Аникин | ЕСЛИ(D2=5;1;0) | ||||||||
| Бабурин | |||||||||
| Демидов | |||||||||
| Иванов | |||||||||
| Казаков | |||||||||
| Леонов | |||||||||
| Орлов | |||||||||
| Петров | |||||||||
| Фролов | Неявка | =СЧЁТЕСЛИ (D10;"НЕЯВКА") | |||||||
| Итого | =СУММ(E2:E10) |
Задание 24.
Определить, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в таблице 14.
Создайте таблицу по образцу.
Создайте таблицу, содержащую четыре интервала числовых значений зарплат: 1000-2000, 2000-3000, 3000-4000, 4000-6000 (таблица 15).
Таблица 14
| № п/п | Ф.И.О. | Зарплата | 1 интервал | 2 интервал | 3 интер. | 4 интер. | Проверка |
| Аникин | |||||||
| Бабурин | |||||||
| Демидов | |||||||
| Иванов | |||||||
| Казакова | |||||||
| Итого |
| № п/п | Ф.И.О. | Зарплата | 1 интервал | 2 интервал |
| Аникин | =ЕСЛИ(И($C2>$B$10;C2<=$C$10);1;0) | |||
| Бабурин | =ЕСЛИ(И(C3>$B$10;C3<=$C$10);1;0) | |||
| Демидов | ||||
| Иванов | ||||
| Казакова | ||||
| Итого | =СУММ(D2:D6) |
Таблица 15
| A | B | ||
| Интервалы | |||
| 1 интервал | |||
| 2 интервал | |||
| 3 интервал | |||
| 4 интервал |
1. Создайте новую рабочую книгу.
2. Создайте таблицу из восьми столбцов, в которой содержатся сведения о семи сотрудниках НИИ: № п/п, Ф.И.О., ежемесячная зарплата (табл. 14).
3. Создайте таблицу, содержащую четыре интервала числовых значений зарплат: 1000-2000, 2000-3000, 3000-4000, 4000-6000 (табл. 15).
4. Чтобы определить, попадает ли значение зарплаты из столбца С в заданный интервал, нужно использовать логическую функцию ЕСЛИ с заданием сложного условия И. Для этого необходимо выполнить следующее:
- установить курсор в ячейку D2;
- щелкнуть на значке Вставка функции Стандартной панели инструментов;
- в окне Мастера функций выбрать Категорию функции Логические, в окне Вид функции – выбрать функцию ЕСЛИ, нажать кнопку ОК;
- в адресной строке рабочего окна в раскрывающемся списке выбрать функцию И;
- установить курсор в поле Логическое 1;
- на рабочем поле Excel щелкнуть на ячейке С2;
- с клавиатуры ввести >;
- на рабочем поле Excel щелкнуть на ячейке А10;
- установить курсор в поле Логическое 2;
- на рабочем поле Excel щелкнуть на ячейке С2;
- с клавиатуры ввести <;
- на рабочем поле Excel щелкнуть на ячейке В10; не закрывая окно Функции И, щелкнуть на слове Если в адресной строке рабочего окна – откроется окно функции Если; в поле Значение_если_истина с клавиатуры ввести 1; в поле Значение_если_ложъс клавиатуры ввести 0; нажать кнопку ОК.
D2=ЕСЛИ(И(C2>$A$10;C2<=$B$10);1;0)
5. Формулу из ячейки D2 операцией автозаполнения скопировать по столбцу D, ссылки на ячейки А10 и В10 нужно сделать абсолютными.
6. Аналогичным образом введите формулы в столбцы Е, F, G.
7. Для подсчета числа попаданий в каждый интервал выполните следующие действия:
выделите блок D2:D6;
нажмите кнопку Автосумма на Стандартной панели инструментов;
повторите это действие для каждого столбца.
8. Значения столбца Проверка получите, используя операцию Автосумма для значений блоков строк D2:G2, D3:G3 и т.д.
9. Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.