Функция поиска данных в некотором диапазоне
ПРОСМОТР(ИСКОМОЕ ЗНАЧЕНИЕ;ДИАПАЗОН) –группа функций ссылки и массивы. Диапазон представляет из себя блок, состоящий из двух колонок. Поиск ведется по искомому значению в первой колонке диапазона, а в текущую ячейку выбирается соответствующее значение из второй колонки диапазона. Если точное соответствие не обнаружено, то выбирается наибольшее значение в диапазоне, меньшее или равное искомому.
Ø Введите в ячейки I1:I12 цифры от 1 до 12;
Ø Используя автозаполнение введите в ячейки J1:J12 названия месяцев с января по декабрь. В ячейку К9 введите любое число от 1 до 12.
Ø В ячейку L9 вставьте функцию ПРОСМОТР, выбрав первый способ задания аргументов (искомое значение; вектор просмотра; вектор результата): = ПРОСМОТР(K9;$I$1:$I$12;$J$1:$J$12). В ячейке L9 появится название соответствующего месяца.
Примечание:Для того чтобы быстро ввести абсолютные ссылки на адреса ячеек введите сначала этот адрес без знака $, например I1, затем нажмите F4 и у вас вместо названия ячейки I1 появится абсолютная ссылка на эту ячейку $I$1.
Функция поиска по условию.
Сочетание двух функций ЕСЛИ и ПРОСМОТР.
Пример: необходимо в какую либо таблицу вставить наименование изделия по ее коду.
Ø Вставьте лист и назовите его Справочник,введите на этом листе следующую таблицу: Таблица 8. Справочник изделий.
|
|
|
| Код изделия | Наименованиеи изделия |
| 98 | Редуктор |
| 99 | КПП |
| 101 | Двигатель |
| 110 | Муфта |
| 156 | Форсунка |
| 157 | Мультипликатор |
| 160 | Замок |
Ø Войдите в лист Деталь-сорт,восстановите в нем столбец В, содержащий коды изделийи вставьте в нем столбец С. В шапку таблицы в столбец С, (который вы вставили) введите "Наименование изделия"
Ø Установите курсор в ячейке С3 и в Мастере функций по шагам введите в эту ячейку следующую функцию:
В окне функции ЕСЛИ:
1) В поле Логическое_выражение введите В3=""
2) В поле Значение_если_истина введите ""
3) В поле Значение_если_ложь введите функцию ПРОСМОТР(1-й вариант: искомое_значение; вектор_просмотра; вектор_результата. )
В окне функции ПРОСМОТР:
1) В поле Искомое_значение введите адрес ячейки В3
2) В поле Вектор_просмотра введите: Справочник!$А$3:$A$9
3) В поле Вектор_результата введите: Справочник!$В$3:$В$9
При этом окно ввода функции будет иметь вид: (см. рис. 13). После ввода последнего аргумента функции нажмите клавишу Enter.
В результате этих действий в ячейке С3 получится "редуктор"
Ø Скопируйте эту функцию из ячейки С3 в ячейки С4:С15.
Таким образом на листе Деталь-сорт должна получиться следующая таблица (см. табл. 9).
|
|
|
Словами эту сложную функцию можно описать так: "Если значение ячейки В3 – "пусто", то и ячейка С3, в которую вводится функция, должна быть тоже "пустой", иначе (если ячейка В3- не пустая) выполнить ПРОСМОТР таким образом: сначала посмотреть, что находится в ячейке В3 ( искомое значение) на листе Деталь-сорт, затем просмотреть все ячейки А3:А9 на листе Справочник, сравнивая их значения с тем значением, которое находится в ячейке В3 (на листе Деталь-сорт). Т.е. с помощью функции ПРОСМОТР по коду детали находится ее наименование и вставляется в текущую ячейку.
 |
Рис.13. Ввод функции ЕСЛИ с вложенной в нее функцией ПРОСМОТР
Таблица 9. Ведомость расчета потребности в деталях
| Код детали | Код изделия | Наименование Изделия | Потребность в деталях, шт. | Себестоимость руб./шт. | Себестоимость выпуска, руб. |
| 121201 | 98 | Редуктор | 80 | 120 | 9600 |
| 121201 | 101 | Двигатель | 25 | 120 | 3000 |
| 121201 | 110 | Муфта | 11 | 120 | 1320 |
| 121300 | 98 | Редуктор | 70 | 450 | 31500 |
| 121300 | 110 | Муфта | 40 | 450 | 18000 |
| 121300 | 156 | Форсунка | 105 | 450 | 47250 |
| 121300 | 157 | Мультипликатор | 23 | 450 | 10350 |
| 121302 | 98 | Редуктор | 300 | 500 | 150000 |
| 121302 | 99 | КПП | 50 | 500 | 25000 |
| 121302 | 101 | Двигатель | 100 | 500 | 50000 |
| 121302 | 156 | Форсунка | 35 | 500 | 17500 |
| 121302 | 157 | Мультипликатор | 25 | 500 | 12500 |
| 121302 | 160 | Замок | 24 | 500 | 12000 |
|
|
|
2.17. Функция поиска по условию и по последним трем цифрам с помощью функции ОСТАТ.
Ø На листе Справочниквведите в ячейки А12:В15 следующую таблицу (таблица 10)
Таблица 10. Справочник деталей
| Код Детали | Наименование деталей |
| 201 | болт |
| 300 | гайка |
| 302 | шайба |
Ø На листе Деталь-2вставьте столбец В и в шапку таблицы в этом столбце введите "Наименование деталей".
Ø Установите курсор в ячейку В3 и введите в нее функцию ЕСЛИ с двумя вложенными в нее функциями: ПРОСМОТР â а в ней ОСТАТ
Ø Заполните поля функции ЕСЛИ также как и в предыдущем случае:
1) В поле Логическое _выражение ведите: А3=""
2) В поле Значение_если_истина введите: ""
3) В поле Значение если ложь введите функцию ПРОСМОТР
Ø Поля функции просмотр заполните следующим образом :
1)В поле искомое значение введите функцию ОСТАТ
Ø Поля функции ОСТАТ заполните так: в поле Число введите А3, в поле Делитель введите 121000.
Таким образом, при делении числа в столбце А на 121000 остаток от деления будет показывать последние 3 цифры ( в ячейке А3 результат деления будет равным 201)
|
|
|
Ø После ввода функции ОСТАТ установите курсор в строку формул перед последней скобкойи вы вернетесь в окно функции ПРОСМОТР. В этом окне продолжите заполнять остальные поля
2) В поле Вектор_просмотра введите: Справочник!$A$13:$A$15
3) В поле Вектор_результата введите: Справочник!$B$13:$B$15
В результате ввода этой сложной функции в ячейке В3 получится слово "болт".
Ø Скопируйте эту функцию в ячейки В4:В15.
Ø Скопируйте из любой существующей в вашей книге таблицы столбец "Код изделия", который был удален при выполнении консолидации.
Ø Введите в таблицу новый столбец "Наименование изделия" .
Ø Выполните самостоятельно заполнение этой графы, с помощью функций ЕСЛИ и ПРОСМОТР.
Окончательно ваша таблица на листе Деталь-2должна выглядеть следующим образом: (см. таблицу 11).
Таблица 11. Ведомость расчета потребности в деталях.
| Код детали | Наименование детали | Код изделия | Наименование изделия | Потребность в деталях, шт. | Себестоимость руб./шт. | Себестоимость выпуска, руб. |
| 121201 | болт | 98 | редуктор | 80 | 120 | 9600 |
| 121201 | болт | 101 | двигатель | 25 | 120 | 3000 |
| 121201 | болт | 110 | муфта | 11 | 120 | 1320 |
| 121300 | гайка | 98 | редуктор | 70 | 450 | 31500 |
| 121300 | гайка | 110 | муфта | 40 | 450 | 18000 |
| 121300 | Гайка | 156 | форсунка | 105 | 450 | 47250 |
| 121300 | Гайка | 157 | мультипликатор | 23 | 450 | 10350 |
| 121302 | шайба | 98 | редуктор | 300 | 500 | 150000 |
| 121302 | шайба | 99 | КПП | 50 | 500 | 25000 |
| 121302 | шайба | 101 | двигатель | 100 | 500 | 50000 |
| 121302 | шайба | 156 | форсунка | 35 | 500 | 17500 |
| 121302 | шайба | 157 | мультипликатор | 25 | 500 | 12500 |
| 121302 | шайба | 160 | замок | 24 | 500 | 12000 |
Функция БС
Функция БСпредназначена для расчета будущей стоимости периодических постоянных платежей и единой суммы вклада или займа на основе постоянной процентной ставки. Относится к группе финансовых функций.
БС – будущее значение, возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки.
Синтаксис: БС(СТАВКА;КПЕР;ПЛАТА;НЗ;ТИП).
СТАВКА–это процентная ставка за период.
КПЕР – это общее число периодов выплат годовой ренты.
ПЛАТА – это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно плата состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов.
НЗ – это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент НЗ опущен (проигнорирован), то он полагается равным 0.
ТИП– это число 0 или 1, обозначающее, когда должна производиться выплата: 0 – в конце периода, 1 – в начале периода. Если аргумент опущен, то он полагается равным 0.
Для аргументов СТАВКА И КПЕР используются согласованные единицы измерения. Если производятся ежемесячные платежи по четырехгодичному займу из расчета 12% годовых, то СТАВКА должна быть 12%/12, а КПЕР должно быть 4*12. Если производится ежегодные платежи по тому же займу, то СТАВКАдолжна быть 12%, а КПЕРдолжно быть 4.
Все аргументы, означающие деньги, которые вы платите (например, депозитные вклады), представляются отрицательными числами; деньги, которые вы получаете (например, дивиденды), представляются положительными числами.
Пример, вы собираетесь вложить 1000 руб. под 6% годовых ( что составит в месяц 6%/12 или 0,5%). Вы собираетесь вкладывать по 100 руб. в начале каждого следующего месяца в течение следующих 12 месяцев. Сколько денег будет на счету в конце 12 месяцев?.
Ø Откройте лист Функции, установите курсор в ячейку Е18 . Вызовите Мастер функций, в поле Категории выберете Финансовые функции и в поле Функция выберете функцию БС. В появившемся окне заполните поля следующим образом:
Ø В поле Норма введите 0.5%.В поле Число_периодов (КПЕР) введите 12
Ø В поле Выплата (ПЛАТА) – введите -100. В поле НЗ введите -1000
Ø В поле Тип введите 1.
В результате в ячейке Е14 должно получиться 2301,40руб.
В качестве аргументов этой функции могут быть не числа, а адреса ячеек таблицы.
Функция ДЕНЬНЕД
Функция предназначена для определения номера дня недели для даты, заданной в числовом формате, возвращает число от 1 до 7.
ДЕНЬНЕД(ДАТА;ТИП)
ДАТА – дата, заданная в числовом формате (например:12/01/2006 или 30.11.2006).
ТИП - значение 1 (с Вс=1 по Сб=7), 2 (сПн=1 по Вс=7), 3 (с Пн=0 по Вс=6)
В ячейку А25 введите дату 30.11.2006, в ячейку В25 введите функцию:
ДЕНЬНЕД(А25;2)
В результате в ячейке В25 должно получиться число 4, соответствующее четвергу.
В ячейке А19 введена Ваша дата рождения. Определите, в какой день недели Вы родились.
Дата добавления: 2018-04-05; просмотров: 209; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!