Создание электронных таблиц, ввод формул. Мастер функций

Вычислить обратную матрицу С-1 для матрицы С, где

С =

Решение:

Оформим на листе матрицу А в виде таблицы:

Выделим диапазон ячеек там, где будет находиться обратная матрица. При помощи Мастера функций введем формулу =МОБР(B1:E3):

Нажамем F2 и затем комбинацию клавиш Ctrl+Shift+Enter. В выделенном диапазоне ячеек появится обратная матрица:

MS Excel: использование относительной и абсолютной ссылок в формулах

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

Таблица будет иметь следующий вид:

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

Решение:

Создадим таблицу. Данные в В2 =ПИ()*LN(A2+$B$1^2), растянем до ячейки В11. Другие ячейки заполним аналогично. Выделим таблицу с данными и установим формат ячеек «Числовой» и число десятичных знаков «2».

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

MS Excel: использование логических функций в формулах

Решить задачу из примера №1 для другого запроса работодателя.

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

Решение:

Создадим таблицу по заданию. Для отбора из этого списка кандидатов, соответствующих требованиям работодателя, воспользуемся логической функцией ЕСЛИ. Если кандидат из списка соответствует требованиям, то вывести в отдельном столбце слово «подходит», а иначе - слово «нет».

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

=ЕСЛИ(B2="М";ЕСЛИ(C2="с/с";"Подходит";"Нет");ЕСЛИ(C2="В";"Подходит";"Нет"));

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

Построение диаграмм, графиков и поверхностей

В таблице приведены данные о выработке предприятия по кварталам за год:

Квартал	I	II	III	IV
Выработка	11	13	15	9

Представьте эти данные в виде:

а) гистограммы

б) объемной гистограммы

в) кольцевой диаграммы

г) круговой диаграммы

д) объемной круговой диаграммы.

Решение:

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

Объемная гистограмма:

Кольцевая диаграмма:

Круговая диаграмма:

Объемная круговая диаграмма:

Создание базы данных в MS Excel. Сортировка и фильтрация данных

Возьмите из №5.7. таблицу крупнейших рек мира. Заполнить с помощью формул пустые ячейки.

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

С помощью Автофильтра выберите из таблицы самые крупные реки Азии с длиной более 5000км.

С помощью расширенного фильтра выберите из таблицы реки Африки с площадью бассейна более 3000км2.

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

Решение:

Создадим таблицу по заданию и заполним с помощью формул (С10=МИН(C2:C9), С11=МАКС(C2:C9), С12=СРЗНАЧ(C2:C9) и также для D10-D12) пустые ячейки:

Произведем сортировку рек одновременно в порядке убывания по географическому положению и по увеличению их площади бассейна:

С помощью Автофильтра выберем из таблицы самые крупные реки Азии с длиной более 5000км:

Выберем из таблицы реки Африки с площадью бассейна более 3000км2, для этого зададим диапазон условий: в ячейку В56 введем =B44="Африка", в D56=D44>3000 и с помощью расширенного фильтра получим:

Отобразим реки, у которых длина меньше средней длины всех рек таблицы, для этого зададим диапазон условий C75=C60<СРЗНАЧ($C$60:$C$67) и применим расширенный фильтр:

Способы консолидации данных. Сводные таблицы

Создайте рабочую книгу, состоящую из листов: Беларусь, Украина, Россия и заполните их следующими данными

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

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

Решение:

Создадим следующие листы: Россия, Беларусь, Украина. Применим фунцкию «Консолидация» на листе Консолидация для выполнения консолидации платежей по городам:

Выполним консолидацию всех наличных и безналичных платежей по странам с помощью функции «Консолидация»:

На листе «КонсолидацияСвязь» выполним консолидацию платежей по городам, установив связь с исходными данными:

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

Построим сводную таблицу с помощью функции в меню «Данные - Сводная таблица» (в нескольких диапазонах консолидации)

Функция автоматизации расчетов. Подбор параметров

Используя "Подбор параметра" решить уравнение: ln(x)=cos(x)

(Для определения корней необходимо учесть область определения функций)

Решение:

Для задания области определения функции в ячейку В2 введем формулу =LN(A2)-COS(A2), А2 = 0,5, В2 =LN(A2)-COS(A2) растянем ячейки А и В до 10.

Построим график функции:

Корень уравнения близок к 1,3. С помощью меню «Сервис - Подбор параметров» вычислим корень уравнения: х=1,302.

excel функция таблица график

Использование средств «Поиск решения»

Из круглой жестянки радиуса R изготавливается коническое пожарное ведро

Образец изготовления конического пожарного ведра

Технология изготовления: из жестянки вырезается сектор с углом , а остальная часть сворачивается в конус. Получается конус с высотой h и радиусом основания r. И высота h получившегося конуса и радиус его основания r зависят от радиуса заготовки R и угла :

Радиус жестянки, из которой делается ведро R = 40 см. Найдите при каком угле объём V пожарного ведра будет максимальным.

Vконуса = r2h/3

Решение:

Создадим таблицу. Угол определим в ячейке А2. Радиус жестянки в ячейке В2 (R=40). Радиуса основания С2=B2*(1-A2/360). В D2 введем формулу для расчета высоты =КОРЕНЬ((B2)^2-(C2)^2). В Е2 введем формулу для расчета объема пожарного ведра =(ПИ()*(C2)^2*D2)/3.

С помощью функции «Поиск решения» определим, при каком угле объём V пожарного ведра будет максимальным. Установим целевую ячейку $E$2, равной максимальному значению. Установим ограничения (угол 360>=>=0) , в результате получим:

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

Поиск решения. Решение оптимизационных задач

Фирма по производству моющих средств рекламирует свою продукцию в Интернете, по телевидению, на радио и в печатных изданиях. Затраты на рекламу ограничены 10000$ ежемесячно. При этом один блок рекламы по телевидению стоит в 10 раз дороже, чем по радио, в 5 раз дороже, чем в печатных изданиях и в 50 раз дороже рекламы по Интернету. При этом исследования показали, что эффективность рекламы по Интернету в 3 раза выше, чем в печатных изданиях и в 2 раза эффективнее, чем по радио. Рекламировать товар необходимо во всех источниках средств массовой информации. Определите ежемесячное оптимальное распределение вложений в рекламу.

Решение:

Создадим следующую таблицу:

Стоимость рекламы по телевидению = 100. В ячейку В2 введем =C3/50, в D2 = C3/10, в E2 = =C3/5. Эффективность рекламы по интернет = 6, тогда по телевидению = B4, по радио = B4/2, в печатных изданиях =B4/3. Лимит возможных затрат = 10000. Введем формулу итоговых затрат =СУММПРОИЗВ(B2:E2;B3:E3). В строку «Целевая функция» введем =СУММПРОИЗВ(B2:E2;B4:E4).

Воспользуемся функцией «Поиск решения»: внесем необходимые значения и ограничения: лимита на рекламу и условие необходимости размещения рекламы во всех источниках средств массовой информации.

В пункте Параметры устанавим параметры «Линейная модель» и «Неотрицательные значения»:

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

Создание форм в MS Excel с использованием элементов управления

Создать пользовательскую форму по образцу. Данные взять свои не менее 5 наименований по каждому виду техники.

Решение:

Создадим новые листы «Задание №10.6 (Ремонт комнаты)», «Потолок», «Стены», «Пол», «Окна», «Двери». Определим текущую дату В3 =СЕГОДНЯ(),текущий курс доллара D3=32,4 рублей.

Внесем наименование и цену материалов и работ в листе «Потолок». Таким же образом оформим остальные листы

Напротив «Потолок» поместим элемент управления «Поле со списком» для выбора потолка из списка, расположенного на листе «Потолок». В окне «Формат элемента управления» выберем вкладку «Элемент управления» и установим следующие параметры:

В ячейку В5 ввести формулу вывода стоимости выбранной техники =ИНДЕКС(Потолок!B2:B11;E5). Такие же данные внесем в ячейки B7, B9, B11, B13. В строке «сумма,$» введем В15 =СУММ(B5:B13).

Установим элемент «Флажок» напротив «Срочность» и изменим текст надписи на «Срочно». Свяжем с ячейкой $E$17.

Данные в ячейках: в ячейку В17 введем =ЕСЛИ(E17=ИСТИНА;120;0). В ячейку В19 введем =B15+B17. В ячейку В21 введем =B19*D3.

ункции EXCEL для финансовых расчетов по ценным бумагам: функции для расчетов по ценных бумагам с нарушением периодичности выплаты процентов (ДОХОДПЕРВНЕРЕГ(), ДОХОДПОСЛНЕРЕГ()).

Рисунок 1. - Окно аргументов функции

Функция ДОХОДПЕРВНЕРЕГ() возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) первым периодом.

Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку «Пакет анализа».

ДОХОДПЕРВНЕРЕГ (дата_согл; дата_вступл_в_силу; дата_выпуска; первый_купон; ставка; цена; погашение; частота; базис)

Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для 23_го мая 2008 года следует использовать ДАТА (2008; 5; 23). Проблемы могут возникнуть, если даты вводятся как текст.

Дата_согл - дата расчета за ценные бумаги (более поздняя, чем дата выпуска, когда ценные бумаги были проданы покупателю).

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

Дата_выпуска - дата выпуска ценных бумаг.

Первый_купон - дата первого купона для ценных бумаг.

Ставка - процентная ставка для ценных бумаг.

Цена - стоимость ценных бумаг.

Погашение - выкупная стоимость ценных бумаг за 100 грн. номинальной стоимости.

Частота - количество выплат по купонам за год. Для ежегодных выплат частота = 1; для полугодовых выплат частота = 2; для ежеквартальных выплат частота = 4.

Базис - используемый способ вычисления дня.

· Microsoft Excel хранит даты как ряд последовательных номеров, что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января 2008 - номер 39448, так как интервал в днях между этими датами составляет 39448.

· Дата соглашения является датой продажи покупателю купона, например облигации. Срок платежа представляет собой дату истечения срока действия купона. Пусть, например, облигация со сроком действия 30 лет выпущена 1 января 2008 года и была приобретена покупателем через шесть месяцев после своего выпуска. Датой выпуска будет являться 1 января 2008 года, датой соглашения - 1 июля 2008 года, а сроком погашения такой облигации - 1 января 2038 года, то есть дата через 30 лет после даты выпуска.

· Дата_согл, дата_вступл_в_силу, дата_выпуска, первый_купон и базис усекаются до целых.

· Если дата_согл, дата_вступл_в_силу, дата_выпуска или первый_купон не является допустимой датой, то функция ДОХОДПЕРВНЕРЕГ возвращает значение ошибки #ЗНАЧ!.

· Если ставка < 0 или цена ? 0, то функция ДОХОДПЕРВНЕРЕГ возвращает значение ошибки #ЧИСЛО!.

· Должны быть выполнены следующие условия, в противном случае функция ДОХОДПЕРВНЕРЕГ возвращает значение ошибки #ЧИСЛО!:

дата_вступл_в_силу > первый_купон > дата_согл > дата_выпуска

· Microsoft Excel вычисляет функцию ДОХОДПЕРВНЕРЕГ методом итераций. Используется метод Ньютона на основе формулы для функции ЦЕНАПЕРВНЕРЕГ. Доход вычисляется за 100 итераций, до тех пор, пока вычисляемая цена для заданного дохода не станет близкой к значению аргумента цена.

Пример

Чтобы просмотреть числа в виде процентов, выделите ячейку и выберите в меню Формат команду Ячейки. На вкладкеЧисло выберите в списке Числовые форматы вариант Процентный.

Доходпослнерег

Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) последним периодом.

ДОХОДПОСЛНЕРЕГ (дата_согл; дата_вступл_в_силу; последняя_выплата; ставка; цена; погашение; частота; базис)

Даты должны вводиться с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для 23_го мая 2008 года следует использовать ДАТА (2008; 5; 23). Проблемы могут возникнуть, если даты вводятся как текст.

Дата_согл - дата расчета за ценные бумаги (более поздняя, чем дата выпуска, когда ценные бумаги были проданы покупателю).

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

Последняя_выплата - дата последнего купона для ценных бумаг.

Ставка - процентная ставка для ценных бумаг.

Цена - стоимость ценных бумаг.

Погашение - выкупная стоимость ценных бумаг за 100 грн. номинальной стоимости.

Частота - количество выплат по купонам за год. Для ежегодных выплат частота = 1; для полугодовых выплат частота = 2; для ежеквартальных выплат частота = 4.

Базис - используемый способ вычисления дня.

Базис	Способ вычисления дня
0 или опущен	Американский (NASD) 30/360
1	Фактический/фактический
2	Фактический/360
3	Фактический/365
4	Европейский 30/360

· Microsoft Excel хранит даты как ряд последовательных номеров, что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 года имеет номер 1, а 1 января 2008 - номер 39448, так как интервал в днях между этими датами составляет 39448.

· Дата соглашения является датой продажи покупателю купона, например облигации. Срок платежа представляет собой дату истечения срока действия купона. Пусть, например, облигация со сроком действия 30 лет выпущена 1 января 2008 года и была приобретена покупателем через шесть месяцев после своего выпуска. Датой выпуска будет являться 1 января 2008 года, датой соглашения - 1 июля 2008 года, а сроком погашения такой облигации - 1 января 2038 года, то есть дата через 30 лет после даты выпуска.

· Дата_согл, дата_вступл_в_силу, последняя_выплата и базис усекаются до целых.

· Если дата_согл, дата_вступл_в_силу или последняя_выплата не является допустимой датой, то функция ДОХОДПОСЛНЕРЕГ возвращает значение ошибки #ЗНАЧ!.

· Если ставка < 0 или цена ? 0, то функция ДОХОДПОСЛНЕРЕГ возвращает значение ошибки #ЧИСЛО!.

· Если базис < 0 или базис > 4, то функция ДОХОДПОСЛНЕРЕГ возвращает значение ошибки #ЧИСЛО!.

· Должны быть выполнены следующие условия, в противном случае функция ДОХОДПОСЛНЕРЕГ возвращает значение ошибки #ЧИСЛО!:

дата_вступл_в_силу > дата_согл > последняя_выплата

· ДОХОДПОСЛНЕРЕГ вычисляется следующим образом:

·

где:

Ai = количество накопленных дней для i_го или последнего квазикупонного периода в нерегулярном периоде, отсчитанное вперед от даты последней выплаты перед погашением.

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

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

NLi = нормальная продолжительность в днях i_го или последнего квазикупонного периода в нерегулярном купонном периоде.