Основы работы в MS Excel
Создание
электронных таблиц, ввод формул. Мастер функций
Вычислить обратную матрицу С-1 для матрицы С, где
С
=
Решение:
Оформим
на листе матрицу А в виде таблицы:
Выделим диапазон ячеек там, где будет находиться обратная матрица. При
помощи Мастера функций введем формулу =МОБР(B1:E3):
Нажамем F2 и затем комбинацию клавиш Ctrl+Shift+Enter. В выделенном
диапазоне ячеек появится обратная матрица:
MS Excel:
использование относительной и абсолютной ссылок в формулах
Составить
матрицу размером 10´10 элементов, в которой элементы матрицы определяются
по формуле:
Таблица
будет иметь следующий вид:
Полученные результаты отформатировать до 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 изготавливается коническое пожарное ведро
Образец
изготовления конического пожарного ведра
Технология
изготовления: из жестянки вырезается сектор с углом a, а остальная часть сворачивается в конус. Получается конус с высотой h
и радиусом основания r. И высота h получившегося конуса и радиус его основания
r зависят от радиуса заготовки R и угла a:
Радиус
жестянки, из которой делается ведро R = 40 см. Найдите при каком угле a объём V пожарного ведра будет максимальным.
Vконуса = pr2h/3
Решение:
Создадим
таблицу. Угол a определим в ячейке А2. Радиус жестянки в ячейке В2 (R=40).
Радиуса основания С2=B2*(1-A2/360). В D2 введем формулу для расчета
высоты =КОРЕНЬ((B2)^2-(C2)^2). В Е2 введем формулу для расчета объема пожарного
ведра =(ПИ()*(C2)^2*D2)/3.
С
помощью функции «Поиск решения» определим, при каком угле a объём V пожарного ведра будет максимальным. Установим целевую ячейку $E$2,
равной максимальному значению. Установим ограничения (угол 360>=a>=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.
Библиографический список
1. Информатика.
Базовый курс. 2-е издание / Под ред. С.В. Симоновича. - СПб.: Питер, 2004. -640
с.: ил.
2. Практикум
по экономической информатике. Ч. 1,2,3. Под ред. Шуремова Е. Л., М. 2004г.
. Информатика
для юристов и экономистов. Под ред. Симоновича С. В., М. 2004г.
. Эффективная
работа: Word 2002/М. Миллхоллон, К. Мюррей. - СПб.: Питер, 2003
. Ехсеl
сборник примеров и задач. М. 2003г.
. Саймон
Д. Анализ данных в Ехсе1. М. 2004г.
. Н.Коцюбинский
А. О. Ехсеl для бухгалтера в примерах. М. 2003г.
. Понятный
самоучитель работы в Ехсеl. СП. 2004г.