ФУНКЦИИ ПОИСКА (функции горизонтального и вертикального просмотра)

ОРГАНИЗАЦИЯ ДАННЫХ В EXCEL

Книга–файл имеет расширение имени XLS. По умолчанию книги получают имена Книга1.xls, Книга2.xls и т.д. (расширение указывать необязательно). Число книг ограничено только размером диска. Книга содержит рабочие листы.

Рабочий лист образует рабочее пространство пользователя. Листов в книге может быть до 255 (по умолчанию три). Лист имеет 256 колонок и 65536 строк (самый большой адрес – IV65536). Т.е. всего на листе 16 777 216 ячеек. Листам можно давать произвольные имена. По умолчанию они получают имена вида Лист1, Лист2 и т.д.

Клетка – элементарная единица данных в таблице. К ней можно обратиться по адресу, который состоит из буквы – номера колонки и цифры – номера строки, например D25 (клетка на пересечении столбца D и строки 25).

Блок(прямоугольная область клеток). Задается адресами левого верхнего и правого нижнего его углов, разделенными двоеточием, например D4:F20.

ТИПЫ ДАННЫХ

1).Числовые– дробные, целые, с буквой Е. Целая часть от дробной отделяется запятой. 2).Символьные– буквы, слова.

3). Даты– начиная с 1.1.1900г. Даты – это целые числа, представленные в формате даты. Даты вводятся в виде цифр, разделенных точкой. Возможно указание и времени. Поддерживается арифметика дат (допустимо их сложение и вычитание).

МАНИПУЛЯЦИИ ДАННЫМИ

ВЫДЕЛЕНИЕ является важнейшим действием. Преобразование данных осуществляется только над выделенными (указанными) объектами. Выделение клетки осуществляется переходом в нее с помощью клавиатуры или мыши. Выделенная клетка обрамляется жирной линией с квадратной точкой в нижнем правом углу – маркером заполнения. На рис. 1 выделена клетка В2. Содержимое этой клетки будет отображаться в строке формул. Выделение строки/столбца произойдет, если щелкнуть мышью на цифре-номере строки или букве-номере столбца. Выделение листа целиком осуществляется щелчком мыши на кнопке “выде­лить все”. Выделение блока начинается с установки курсора в любом из четырех углов блока. Далее, удерживая клавишу Shift, с помощью клавиш перемещения выделяется остальная часть блока, который при этом (кроме текущей клетки) окрашивается темным цветом. Для выделения блока мышью следует щелкнуть в одном углу блока и, удерживая кнопку, отбуксировать курсор в противоположный угол блока (рис. 2). Курсор при этом должен сохранять вид . Выделение несмежных областей листа может осуществляться мышью аналогично при нажатой клавише Ctrl.

ПЕРЕМЕЩЕНИЕ клетки/блока удобно выполнять с помощью мыши. Для этого курсор следует приблизить к любой границе выделенного блока, так чтобы он превратится в пустую и, удерживая левую кнопку мыши, отбуксировать блок на новое место. На рис. 3 изображены исходный и конечный виды перемещаемой области A1:B2.

КОПИРОВАНИЕ осуществляется аналогично, но при нажатой клавише Ctrl (при этом рядом с курсором-стрелкой появляется маленький знак плюс ). Далее выделенный блок может быть скопирован в любое место (рис. 4).

АВТОЗАПОЛНЕНИЕ (множественное копирование). Мышь фиксируется на маркере заполнения блока (курсор превращается в значок +) и при нажатой кнопке буксируется по горизонтали или вертикали (рис.5).

ФОРМИРОВАНИЕ ПРОГРЕССИЙ. Выполняется как автозаполнение, но с нажатием Ctrl(рис. 6). Курсор превращается в ++.

кнопка “выделить все”

A

B

C

A

B

C

A

B

C

A

B

C

A

B

C

A

B

C

E

E

E

E

+ 1

+ + + 2

3

Рис. 1. Вы- Рис. 2 Рис. 3. Перемещение Рис. 4. Копирование Рис. 5. Заполне- Рис. 6. Прогрессия

делено В2 Выделено В2:C5 A1:B2 в B4:C5 A1:B2 в B4:C5 (+Ctrl) ние E2 в Е3:Е5 E2 в Е3:Е5 (+Ctrl)

ОЧИСТКА освобождает блок от находящихся в нем данных. Осуществляется клавишей Delete. При этом не происходит освобождения ячеек от занесенных в них форматов и элементов оформления (рамок, заливки, объединения ячеек и др.).

УДАЛЕНИЕ – физическое удаление самого объекта (блока, строки, столбца). При этом удаленные объекты замещаются соседними. Удаление выполняется клавишами Ctrl+Минус. Тогда предъявляется окно Удаление ячеек, где можно выбрать способ замещения удаляемых ячеек. Вместо Ctrl+Минус можно воспользоваться контекстным меню (пункт Удалить).

ВСТАВКА. Вставка пустой строки/столбца: выделяется строка/столбец (курсор мыши фиксируется на номере строки/столбца в бордюре) и нажимаются клавиши Ctrl+Плюс. Новая строка будет вставлена сразу над текущей, новый столбец – слева от текущего столбца. Если нужно вставить сразу несколько смежных строк/столбцов, то и выделить следует сразу несколько соответствующих элементов бордюра. Вставка клетки/блока аналогична – выделяется блок нужного размера и положения и нажимается Ctrl+Плюс. В ответ предъявляется окно диалога вставки, где нужно указать, куда именно следует сдвинуть фрагмент таблицы, чтобы принять добавляемый блок. То же можно сделать мышью. После выделения строки/столбца/блока щелчком правой кнопки вызывается контекстное меню где выбирается пункт Вставка.

УПРАВЛЕНИЕ РАЗМЕРОМ СТРОК/СТОЛБЦОВ. Подгонка шириныстолбцов под фактический размер данных выполняется буксировкой границы столбца мышью после установки ее курсора (принимающего вид двунаправленной стрелки), на бордюре таблицы (на вертикальной линии, отделяющей клетки бордюра). Имеется быстрый способ задания минимально возможной ширины столбца/столбцов – двойной щелчок мышью на разделителе клеток бордюра.

ОПЕРАЦИИ С РАБОЧИМИ ЛИСТАМИ. Удаление/вставка листа может быть осуществлена с помощью меню Правка+Удалить лист и Вставка+Лист. Переименование листа реализуется двойным щелчком на вкладке листа внизу экрана после чего можно ввести новое имя. Другой способ – контекстное меню. Перемещение листа. При первоначальном создании листы в книге могут располагаться случайным образом. В последствии у вас появится желание расположить их вкладки в удобном порядке. Это легко сделать отбуксировав вкладку листа в новое место среди других вкладок Копирование листа может быть осуществлено мышью при нажатой Ctrl. Новый лист получает прежнее имя, но с очередным номером (2, 3 и т.д.), которое можно затем изменить.

ВВОД И РЕДАКТИРОВАНИЕ данных может производиться как в строке формул, так и непосредственно в текущей клетке, если предварительно нажать клавишу F2 или дважды щелкнуть мышью. В этом режиме курсор принимает форму вертикальной черты ”½”. По завершении ввода нажмите Enter. При этом курсор переходит на строку ниже. В случае если ввод

производится не по строкам, это неудобно. Тогда для завершения ввода следует пользоваться клавишами со стрелками. Вообще ввод в клетку считается незаконченным, пока редактируемая клетка не покинута любым способом. При этом большинство средств управления Excel остается недоступными. Если в процессе редактирования данных обнаружилось, что были допущены ошибки, можно отказаться от изменения данных, нажав Escape (при условии, что текущая клетка еще не покинута). В случае, если после преобразований данных вы обнаружили, что сделали это неправильно, следует немедленно применить средства отката, нажав на Ctrl+Z, или воспользоваться кнопками отката:

кнопка Отменить. Возврат к старому виду данных после их изменения (возможен откат на глубину до 16-и шагов).

кнопка Вернуть (отмена отката). Используется, если все-таки вы хотите сохранить отмененные изменения.

Каждое нажатие на них позволяют осуществлять откат (отмену отката) одного действия. Чтобы отменить сразу несколько действий, следует нажать соседнюю кнопку 6 , которая откроет перечень, сделанных ранее отмен (до 16-ти). Здесь можно указать именно то действие, по которое включительно должны быть сделаны отмены. Откат доступен и в меню Правка.

СОЗДАНИЕ ФОРМУЛ.Сила электронных таблиц заключается в возможности помещать в них не только данные, но и формулы. Все формулы должны начинаться со знака “=“ и могут включать константы, знаки операций, функции, адреса клеток (например =5+4/35, =12%*D4, =12*А4-SIN(D3)^2). Операции выполняются слева-направо в порядке их приоритетов, которые могут быть изменены круглыми скобками. Примеры формул и соответствующих им выражений:

=7+5^3/(6*8) =A5/(C7-4)+(4+F4)/(8-D5)*2,4 2+Sin2А2 =2+(SIN(А2))^2

Замечания к знаку %. Если вы введете в ячейку число со знаком %, фактическое его значение будет в 100 раз меньше. Например, если введено 5%, запомнится число 0,05. Таким образом, вводится процент, а хранится коэффициент. Это действие эквивалентно установлению процентного формата клетки для числа 0,05.

Ссылки на ячейки могут вводиться непосредственно с клавиатуры, но могут более надежно и более быстро указываться мышью, используемой как указка. Здесь гарантируется правильный ввод, поскольку пользователь непосредственно видит (выделяемые объекты обрамляются бегущей пунктирной линией) и выбирает именно те данные, которые он хочет включить в выражение. Положим нужно ввести в ячейку А1 формулу вида =А2+В4·С2. Здесь следует выполнить следующую (см. таблицу) цепочку действий:

КОПИРОВАНИЕ ФОРМУЛ.Пусть в некоторой ячейке записана формула =B4. При копировании мы обнаружим (см. рис.), что копии отличаются от оригинала. При копировании по горизонтали изменяется номер строки, при копировании по вертикали – номер столбца, при копировании по диагонали изменяются оба. Обычно такая трансформация вполне отвечает желанию пользователя. Ему не приходится вручную изменять формулы для применения их в новом месте (в другой строке/столбце). Но так бывает не всегда. Чтобы предотвратить автоматическое изменение адресов, перед “замораживаемой” координатой следует поставить знак $. Так, формула вида =$A$1 не будет изменена ни при каком копировании ее в таблице. Знак $ может быть установлен и только перед одной координатой. Можно вводить $ непосредственно, а можно (в режиме ввода) с помощью клавиши F4. Ее последовательное нажатие влечет поочередный ввод знака $ перед элементами адреса (A1®$A$1®A$1®$A1®A1).

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

квартплата = тариф_за_коммун._услуги•площадь + тариф_за_газ•человек или D5 =2руб*80м+3руб*4чел

Задание 2. Расчеты по вкладам(сложные проценты).Вычислить прибыль от суммы 1000р (клетка В1) помещенной на 4 года в банк, ежегодная норма прибыли которого 10%, 15%, 20% и 30% соответственно. Механизм начисления:

прибыль = последняя_сумма_на_счету•норма

сумма = предыдущая_сумма_на_счету + доход.

Начиная с 2-го года можно копировать формулы из

В5,С5 вниз.

Задание 3. Повременная зарплата (заданы: дневной тариф – 40руб, размер налога и премии). Решить самостоятельно (заморозив нужные ссылки).

Задание 4. Распределение фонда заработной платы.Распределить зарплату работникам в соответствии с установленным коэффициентом трудового участия. При правильном решении фонд з/п будет в точности равен сумме (B1=C8). Подсказка: сначала найдите сумму всех коэффициентов (В8) и стоимость единицы коэффициента в рублях. Зарплата определяется умножением этой величины на индивидуальный КТУ.

Задание 4а. Вычислить средний баллсдачи экзамена студенческой группой. Результат поместить в любой свободной клетке.

АНАЛИЗ УСЛОВИЙ

да

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

нет

Формат функции: =ЕСЛИ(условие; ответ_если_условие_ИСТИННО; ответ_если_ЛОЖНО).

Пример: Вывести большее из двух чисел А1 и А2. =ЕСЛИ(A1>A2; A1; A2).

Или в графической форме:=ЕСЛИ(A1>A2; –––да® A1;

нет® A2)

Пример: Вывести сообщение о том, какое из них больше =ЕСЛИ(A1>A2; “A1 больше”; “A2 больше”).

Возможно вложение функций ЕСЛИ (до семи). Для удобства формирования условий используются логические функции:

=И(условие1; условие2; …) – функция истинна, если истинны все ее условия.

=ИЛИ(условие1; условие2; …) – функция истинна, если истинно хотя бы одно условие.

=НЕ(условие) – функция истинна, если условие ложно.

Пример: Вывести большее из трех чисел А1, А2 и А3 =ЕСЛИ(И(A1>A2;A1>A3);A1;ЕСЛИ(И(A2>A1;A2>A3);A2;A3)).

Или в графической форме:=ЕСЛИ(И(A1>A2;A1>A3); ––––да® A1;

нет® ЕСЛИ(И(A2>A1;A2>A3); ––––да ® A2;

Нет® A3)).

Задание 5. Премия. Начислим премии работникам, последовательно усложняя условия.

5.1. Пусть, текущий месяц март и женщинам устанавливается премия в размере 300 руб. к 8 марта. Иными словами

премия1=ЕСЛИ(пол=женский, то 300р, иначе 0р).

D3 =ЕСЛИ(B3=”ж”;300;0).

5.2. Такое решение будет правильным только в марте. Если оставить как есть, женщинам будет назначаться премия ежемесячно. Чтобы этого избежать, нужно учесть значение текущего месяца (клетка В1).

премия2 =ЕСЛИ(женщина И март, то 300р, иначе 0р)

Е3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0).

5.3. Пусть также в феврале и мужчинам назначается премия в 300р.

премия3 = ЕСЛИ((женщина И март) ИЛИ (мужчина И февраль), то 300р, иначе 0р)

F3 =ЕСЛИ(ИЛИ(И(B3=”ж”;B$1=3); И(B3=”м”;B$1=2));300;0).

5.4. Премии мужчинам и женщинам различны: мужчинам 10% зарплаты, женщинам 300 руб. независимо от заработка.

а)премия4 = ЕСЛИ(женщина И март, то 300р, ЕСЛИ(мужчина И февраль, то 10% от зарплаты, иначе 0р))

G3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0)). – это вложение функций ЕСЛИ

или несколько проще, если разбить выражение на два слагаемых

б) премия4 =ЕСЛИ(женщина И март, то 300р)+ЕСЛИ(мужчина И февраль, то 10% зарплаты)

G3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0) + ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0). – это сумма функций ЕСЛИ

5.5. Положим, кроме того, всем работникам назначается премия к Новому году в размере 500р. (представим как сумму).

премия5 =ЕСЛИ(женщина И март, то 300)+ЕСЛИ(мужчина И февраль, 10% зарплаты)+ЕСЛИ(январь, 500)

H3 =ЕСЛИ(И(B3=”ж”;B$1=3);300;0) + ЕСЛИ(И(B3=”м”;B$1=2);10%*С3;0) + ЕСЛИ(B$1=1;500;0).

5.6. Решить самостоятельно. Премия 300р. женщинам (независимо от месяца), зарабатывающим менее 1000р.

I3=ЕСЛИ(. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5.7. Решить. Премия всем в мае. Зарабатывающим до 1000р – премия 500р, с 1000р (т.е. при >=1000) – премия в 20% от зарплаты. Сначала как вложение функций ЕСЛИ, затем как сумму

а)J3=ЕСЛИ( . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

б)J3=ЕСЛИ( . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5.8. Решить. Премия (всем независимо от месяца)в размере зарплаты, но не менее 500р и не более 1000р.

а)K3=ЕСЛИ(С3<500;500; ЕСЛИ(С3>1000; . . . . . ; . )) – решить как вложение ЕСЛИ

б)K3=ЕСЛИ(С3<500;500;0) + ЕСЛИ(С3>1000;. . . ;0) + ЕСЛИ(И(С3 . . . . . ;С3 . . . . . ); С3;0)– как их сумму

Задание 6. Сдельная зарплата. Известны: стоимость обработки 1 детали (10р), стоимость испорченной детали (20р). Последняя вычитается из зарплаты. Премия назначается рабочим, которые не имели брака и обработали более 10 деталей. Если брака много, зарплата может оказаться отрицательной и налог, конечно, не взимается. Тарифы в формулах должны быть заданы адресами.

зарплата = Оплата_за_детали – Вычеты_за_брак D4=

премия =ЕСЛИ(брак=0 И деталей>10; то Расчет_премии; иначе 0) E4=ЕСЛИ(

налог=ЕСЛИ(зарплата>0; то Расчет_налога; иначе 0) F4=ЕСЛИ(

выдать = зарплата + премия – налог G4=

Усложним. Если изготовлено >20 деталей, кроме обычной премии дается еще 500р.

A B X Y -6 ? -4 ? -2 ? ? ? ? ? ? ? ? ? ?

E4=ЕСЛИ(...........................................................................................................

Задание 7.Вычислить значения кусочно-ломаной функции Y(X) в диапазоне изменения аргумента Х от -6 до +16 с шагом 2. Х и Y находятся в столбцах А и В.

Здесь Y=ЕСЛИ(X<0;5+X;ЕСЛИ(И(0<=X;X<10);5;10–0,5•X))

или в адресах для первого значения Y и Х (клетки В2 и А2):

B2 =ЕСЛИ(A2<0;5+A2;ЕСЛИ(И(0<=A2;A2<10);5;10–0,5*A2))или проще

B2 =ЕСЛИ(A2<0;5+A2;ЕСЛИ(A2<10;5;10–0,5*A2)).

Упрощение возможно поскольку, если в первом ЕСЛИ условие A2<0 неверно, значит обязательно верно противоположное A2³0, т.о., выяснять это больше не нужно. Вообще, вложенных функций ЕСЛИ должно быть на единицу меньше возможных диапазонов данных. В нашем случае, поскольку ось Х разбита на три подмножества, должно быть два ЕСЛИ.

Задание 8. Расчеты с вкладчиками банка. Пусть годовой банковский процент так зависит от вклада: при вкладе до 10т.руб. составляет 10%, при вкладе до 30т – 15%, до 60т – 20%, если выше – 30%. Т.о.

A B C Вклад Процент   10%   15%   20%   от 60 30%   Расчеты в банке Клиент Вклад Доход Петр ? Иван ? Олег ? ВСЕГО ? ?

доход =ЕСЛИ(вклад<10; то 10%; иначе

ЕСЛИ(вклад>=10 И вклад<30; то 15%; иначе

ЕСЛИ(вклад>=30 И вклад<60; то 20%; иначе 30%)))•вклад или проще

доход=ЕСЛИ(вклад<10; 10%;ЕСЛИ(вклад<30;15%;ЕСЛИ(вклад<60;20%;30%)))•вклад

Записать С8, используя только адреса и сделав необходимое замораживание ссылок

C8=ЕСЛИ(B8<A$2;B$2;ЕСЛИ(B8<

ФУНКЦИИ ПОИСКА (функции горизонтального и вертикального просмотра).

Применяются когда невозможно или неудобно использовать функцию ЕСЛИ.

Функция ГПР. Используется для поиска данных в горизонтальной таблице.

Формат функции: =ГПР(что_искать; где_искать; из_какой_строки_взять_результат; как_искать).

Здесь как_искать(тип поиска)это: 0 – поиск точный.

1 – поиск интервальный, если не найдено точное значение, искомым считается ближайшее меньшее

Поиск ведется всегда в первой строке блока поиска (“где искать”). Результат извлекается из параллельной нижележащей строки с указанным номером относительно первой строки блока (нумерация строк ведется внутри блока, начиная с №1).

Имеется вертикальный аналог функции ГПР – функция ВПР (для работы с вертикально расположенными тарифами)

=ВПР(что_искать; где_искать; из_какого_солбца_взять_результат; как_искать).

Поиск ведется в первом столбце блока поиска. Результат извлекается из параллельного столбца справа с заданным номером.

Задание 9. Повременная оплата с учетом разряда.Рассчитать заработную плату, зависящую от числа отработанных дней в месяце, разряда и премии. Для вычисления собственно зарплаты (область D7:D9) нужно число дней умножить на тариф, зависящий от разряда рабочего

зарплата=тариф_по_разряду•дней.

Для розыска разрядного тарифа понадобится функция ГПР. Так, формула для Петра

зарплата_Петра=ГПР(разряд_Петра; тарифная_сетка; строка_“тариф”; поиск_точный)• днейили

D7 =ГПР(C7;B$2:F$4;2;0)*B7.(=100р•10дн)– строка результата “тариф” имеет №2 внутри блока поиска, строка “премия” ­– №3. Премия также зависит от разряда. Напишите формулу E7=ГПР(. . . . . . . . . . . . . . . . . . . . . . . . . . . .

Другая постановка. Пусть премия дается только при отработке >5 дней

E7=ЕСЛИ(B7>5;ГПР(…);0)*D7 или иначе E7=ГПР(…)*D7*ЕСЛИ(B7>5;1;0).Здесь ГПР(…) это

Еще задача. Отработавшим >25 дней к премии добавляется еще 10%E7= . . . . . . . . . . . . . . . . . . . . . . . . . . .

Задание 10. Конвертирование валюты.При внешнеторговых операциях расчет с поставщиками выполняется в долларах, а с внутренними покупателями – в рублях и нужно конвертировать в рубли исходную (в момент поступления) и текущую (“на сегодня”) цены товара. Информация хранится в двух таблицах: таблице курса доллара (столбцы А,В) и таблице расчета рублевого эквивалента товара. Содержимое первого – последовательные значения дат и цены $. В клетке E1 предъявляется текущая дата. В основной таблице содержатся сведения о дате закупки товара и его закупочной цене в $ (столбцы D и F). В столбце G вычисляется рублевая цена товара на момент покупки, в H – его сегодняшняя рублевая цена. Для столбцов G, H понадобится функция ВПР с четвертым аргументом =1, т.е. поиск даты в курсовой таблице будет не точным, а интервальным, поскольку некоторых дат там нет и стоимость доллара тогда берется равной курсу ближайшей предыдущей даты, для которой она имеется (так, для 9 и 10 января берется курс за 8-е, для 13.янв – за 12-е).

Цена_закупки_в_руб. = Курс_$_на_день_закупки•цена_закупки_в_$или

Цена_закупки_в_руб. = ВПР(дата_закупки; курсовая_таблица; столбец _“цена_$”;

поиск_интервальный)•цена_закупки_в_$или

G3 =ВПР(D3;A$2:B$10;2;1)*F3.– нижняя граница (B10) блока поиска берется с запасом для ввода новых дат и курсов $.

	А	В	C	D	E	F	G	H	I	A B C Доход % налога   12% 18%   свыше 25%   Вычисление налогов Имя Доход Налог Иван ? Петр ? Анна ? J
	Дата	Цена $		Сегодня	13.янв
2	04.янв	20р		Дата закупки	Товар	Цена($) закупки	Цена (р) закупки	Цена (р) сегодня	Цена +30%	Цена+ 40/50%
	05.янв	21р		04.янв	Стол	100$	?р	?р	?р	?р
	06.янв	22р		06.янв	Стул	50$	?р	?р	?р	?р
	07.янв	23р		09.янв	Тумба	80$	?р	?р	?р	?р
	08.янв	24р		10.янв	Шкаф	100$	?р	?р	?р	?р
	11.янв	25р		11.янв	Палас	70$	?р	?р	?р	?р
	12.янв	26р		12.янв	Софа	100$	?р	?р	?р	?р

Вычислить рублевый эквивалент товара на сегодня (столбец H):

Цена_сегодня_в_руб.=Курс_$_на_сегодня• цена_закупки_в_$.Записать H3=ВПР(……………………………….…………….

Усложним задачу. Цена_сегодня, найденная в такой постановке, дает себестоимость товара на сегодня. Чтобы иметь прибыль надо продать его дороже (например на 30%). Напишите формулу в столбце I (не используя содержимое столбца H):

I3=ВПР(................................................................................................................................................

Усложним задачу. Пусть прибыль начисляется в зависимости от цены_закупки. На товар ценой менее 100$ она составляет 50%, на остальные – 40%. Напишите формулув столбце J (можно использовать H):

J3=ЕСЛИ(F3.......................................................................................

Еще. Товар, закупленный более 100 дней назад продается по себестоимости H, остальное, как в J.

К3= ЕСЛИ(..........................................................................................

G

Замечания: 1. Даты следует вводить в числовом виде, так 4.1 будет автоматически преобразовано в 04.янв.

2. Единицы валюты (р и $) непосредственно (руками) вводить нельзя. Они устанавливаются форматированием данных.

Задание 11. Прогрессивный налог.Вычислить годовой подоходный налог с граждан. С дохода до 15т.руб. бе­рется налог в размере 12%, с дохода до 35т – 18%, с дохода свыше 35т – 25%. Причем более высокий налог берется с той частью дохода, которая находится в соответствующем диапазоне. Например, с дохода в 60т, налог будет таков:

		таким образом, для дохода в 60т. руб. налог=12%•15+18%•20+25%•25=11,65т.

Формула в общем виде: налог=ЕСЛИ(доход<15; 12%•доход;

ЕСЛИ(доход<35; 12%•15+18%•(доход-15); 12%•15+18%•(35-15)+25%•(доход-35)))

Записать формулу вычисления налога, используя только адреса (а не константы) и “заморозив” нужные ссылки.

C7=ЕСЛИ(B7<A$2;B$2*B7;ЕСЛИ(B7<A$3; . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Задание 12. Начисление стипендии.В сессию студентами сдавались два экзамена (вводится оценка) и зачета (вводится символ “+”). Неудовлетворительные оценки не выставляются (клетка остается пустой). Вычислить средний балл и стипендию, которая назначается студентам таким образом:

отличники – 3 минимальные зарплаты (МЗ)

сдавшие без троек (хорошисты) – 2 минимальные зарплаты

остальные сдавшие (троечники) – 1 минимальная зарплата

несдавшие – 0

Средний балл (F4) – среднее арифметическое оценок экзаменов, но только если сессия сдана, в противном случае он принимается равным нулю:

средний_балл=ЕСЛИ(сессия_сдана; то (логика+этика)/2; иначе 0)или

средний_балл=ЕСЛИ(логика_сдана И этика_сдана И химия_сдана

И право_сдано; (логика+этика)/2;0) или

средний_балл=ЕСЛИ(логика>2 И этика>2 И химия=”+” И право=”+”; (логика+этика)/2; 0)

Отсюда в виде формулы для первого студента (Петра):

F4 =ЕСЛИ(И(B4>2;С4>2;D4=”+”;E4=”+”); (B4+С4)/2;0).

Средний балл можно вычислить и иначе, исходя от обратного – если не сдана какая-либо из дисциплин, он принимается равным 0, иначе – среднее арифметическое:

средний_балл=ЕСЛИ(сессия_не_сдана; то 0; иначе (логика+этика)/2)или

=ЕСЛИ(логика_не_сдана ИЛИ этика_не_сдана ИЛИ химия_не_сдана ИЛИ право_не_сдано; 0; (логика+этика)/2) или

=ЕСЛИ(логика=0 ИЛИ этика=0 ИЛИ химия=0 ИЛИ право=0; 0; (логика+этика)/2)

F4 =ЕСЛИ(ИЛИ(B4=0;С4=0;D4=0;E4=0); 0; (B4+С4)/2). –пустая клетка считается равной нулю

а можно, используя функцию подсчета занятых клеток СЧЁТЗ, записать и так

F4 =ЕСЛИ(СЧЁТЗ(B4:E4)<4; 0; (B4+С4)/2).

Найдем стипендию:

стипендия=ЕСЛИ(отличник; 3; ЕСЛИ(хорошист; 2; ЕСЛИ(троечник; 1; иначе 0)))•МЗ или

стипендия=ЕСЛИ(ср._балл=5;3;ЕСЛИ(И(логика>3;этика>3;ср._балл>0);2;ЕСЛИ(ср._балл>0;1;иначе 0)))•МЗ

Окончательно для Петра (МЗ вынесем за ЕСЛИ):

G4 =ЕСЛИ(F4=5; 3; ЕСЛИ(И(B4>3;C4>3;F4>0); 2; ЕСЛИ(F4>0;1; 0)))*G$1.

Самостоятельно напишите выражение для стипендии при обратном порядке анализа успеваемости:

стипендия=ЕСЛИ(двоечник; 0; ЕСЛИ(троечник; 1; ЕСЛИ(хорошист; 2; иначе 3)))•МЗ

G4 =ЕСЛИ(. . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .