Вопросы для самоконтроля.

1. Как построить диаграмму с несколькими подписями под осями?

2. Как внести результат вычислений в область?

3. Если использование названий диапазонов запрещено, то где это можно отменить?

4. Как задаются примечания?

5. Если диапазону присвоено имя, то ссылка на имя будет относительной?

6. Требования к заданию имён.

7. Найти произведение чисел, входящих в любой созданный диапазон.

Лабораторная работа № 8

Оформление таблиц. Форматы пользователя.

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

Важное значение при работе с электронными таблицами имеет визуальное представление о данных. Excel имеет богатый арсенал средств в оформлении документов на высоком профессиональном уровне. Варьируя различные виды шрифта, толщину и расположение линий, цвет фона и т.д. можно добиться наглядного представления информации в таблице. При вводе числовых значений по умолчанию используется числовой формат «Основной». При вводе числовой информации содержимому ячейки присваивается один из встроенных числовых форматов, и по умолчанию числа прижимается к правому краю ячейки, если формат не определяется, то вводимое значение воспринимается как текст и по умолчанию прижимается к левому краю ячейки. Для задания числового формата идем в Меню/Формат/Ячейки/Число. В Excel пользователь может создавать собственные числовые форматы. Меню/Формат/Ячейки/Число/все форматы, где можно задавать удобные для вас форматы, свои единицы измерения, а так же цвета для представления чисел.

Порядок выполнения работы: Создать таблицу 8.10 и провести исследование работы фирмы по аналогии с лаб. работой №5 (здесь за базисный период принят 1квартал 2005г)

Таблица 8.10

Характеристики динамических рядов квартальных значений реализации продукции.

			прирост		темп роста, %		темп прироста, %
ГОД	квартал	реализация тыс.руб	Цепной	Базисный	Цепной	Базис-ный	Цепной	Базис-ный
2005	квартал 1	284,835	-	0	-	100,00	-	0,00
2005	квартал 2	224,248	yi-yi-1	yi-yб	yi/yi-1	yi/yб	yi/yi-1-1	yi/yб-1
2005	квартал 3	202,419
2005	квартал 4	260,643
2006	квартал 1	278,836
2006	квартал 2	246,694
2006	квартал 3	188,964
2006	квартал 4	286,469
2007	квартал 1	279,597
2007	квартал 2	258,835
2007	квартал 3	196,768
2007	квартал 4	271,673
2008	квартал 1	284,837
2008	квартал 2	222,246
2008	квартал 3	196,647
2008	квартал 4	265.240
2009	квартал 1	267,183
2009	квартал 2	253,479
2009	квартал 3	216,535
2009	квартал 4	262,324

Результат показать преподавателю и сохранить в свою папку.

После заполнения таблицы, для наглядности полученных результатов, провести дополнительное исследование.

1. Подсчитать количество положительных элементов:

• в цепном приросте

• в базисном приросте

1. Подсчитать количество отрицательных элементов:

• В цепном приросте

• В базисном приросте

Для этого предварительно введем условие по адресу В30: >=0, а по адресу В31: <0

Установите активную ячейку по адресу D30. С помощью вставки функции из категории «Статистические» вызвать функцию «СЧЕТЕСЛИ». В открывшемся окне ввести диапазон исследования – выделить область, где находятся значения цепного прироста; в критерий ввести адрес условия (в первом случае: В30 или >0) «ОК». По аналогии рассчитать остальные значения и сделать выводы об успешности работы фирмы.

3. Вычислить сумму реализаций, но не общую, а сложить числа, отвечающие следующим условиям:

• Если значения реализаций больше 240

• Если значения реализаций меньше или равны 240

Для этого предварительно введем по адресу В32 условие: >240

по адресу В33 условие: <=240

Затем по адресу С32 нужно воспользоваться функцией «СУММЕСЛИ» из категории «Математические» мастера функций. В открывшемся окне нужно указать диапазон (это значения реализаций) и критерий (адрес В32 или >240), диапазон суммирования в нашем случае совпадает с исследуемым диапазоном, поэтому в данном случае его указывать не нужно. «ОК». Аналогично рассчитывается второе значение суммесли().

4. Вычислить сумму реализаций только по вторым кварталам (по адресу С34). Для этого по адресу В34 скопируем любое название «квартал2» из столбца «кварталы», затем снова воспользуемся функцией «СУММЕСЛИ». На этот раз диапазон (исследования) -- это названия всех кварталов; критерий - адрес, где находится «квартал2»; а диапазон суммирования – это значения реализаций. Аналогично рассчитать сумму реализаций для любого квартала.

Результат показать преподавателю и сохранить в свою папку.

Скопировать таблицу на второй лист2

Вставить строки «Итого» по каждому году для вычисления сумм реализаций и строку Всего в конце таблицы для вычисления суммарной реализации за весь период исследования.

5. Оформить таблицу с помощью любого Автоформата.

6. Для значений реализации и цепного и базисного приростов создать пользовательский формат, где все значения, которые больше или равны 200 будут выделены Фиолетовым, положительные значения Синим или Голубым, а отрицательные Красным, значения будут измеряться в $

Excel располагает гибким инструментом по созданию пользовательских форматов. Для наглядности отображения информации можно изменять цвета. Встроенные цвета: [Черный]; [Синий]; [Циан]; [Фиолетовый]; [Красный]; [Белый]; [Желтый], Кроме того можно задавать условия, когда следует применять тот или иной цвет.

Сравнение производится с помощью знаков неравенств.

< меньше

> больше

= равно

<> не равно

<= меньше или равно

>= больше или равно

Примеры: [Желтый][<0]0,000; [Желтый][>0]# ##0,000;

[Синий][>=100]# ##0,00$; # ##0,00$ - означает непосредственно формат числа: перед десятичной точкой обязательно прописывается один разряд, более старшие разряды могут отсутствовать, тысячи разделяются пробелами, после запятой присутствуют два разряда, затем следует единица измерения $. Пробелы между квадратными скобками не ставятся (и внутри тоже), если необходимо прописать несколько условий, то они разделяются «;».

Например: [Зеленый][>=185]# ##0,00$;[Синий][>=0]# ##0,00$ и т.д.

8 . Создать примечание к клетке всего.

9 .Создать рамки с помощью контекстного меню:

10 Построить плоскую линейчатую диаграмму для цепного и базисного приростов.

11 Построить линейную зависимость (график) реализации от времени.

Результат показать преподавателю и сохранить в свою папку.

Для оформления больших таблиц часто требуется, чтобы заголовки повторялись. Конечно, можно применить колонтитулы (Меню/Вид/Колонтитулы или в параметрах страницы). Но есть другой способ. На одном рабочем листе может находиться несколько печатных страниц. Размещение информации по страницам можно увидеть с помощью Меню/Сервис/Параметры, вкладка «Вид», раздел «Параметры окна», поставить галочку «авторазбиение на страницы». На листе2 в начало листа добавьте 1 строку и 1 столбец, по адресу А1 вставьте небольшую картинку. Скопируйте таблицу с расчетами несколько раз вправо и вниз. Теперь нужно задать сквозные строки $1 и сквозные столбцы $A в Меню/Файл/Параметры страницы вкладка Лист. В «Просмотре» проверить, что получилось. Если сделать несколько строк сквозными, то они будут повторяться только для страниц, расположенных под данной, если столбцы сделать сквозными, то они будут видны только для страниц справа от данной. На лист3 Несколько раз скопировать таблицу, сделать сквозными строками ее название и название месяцев.

Результат показать преподавателю и сохранить в свою папку.

Вопросы для самоконтроля.

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

2. Как работает функция «СУММЕСЛИ»? Подсчитать сумму реализаций для квартала 4.

3. Подсчитать количество элементов реализации >250?

4. Чем отличается линейчатая диаграмма от гистограммы?

5. Что такое колонтитулы? Создать колонтитул «Лабораторная работа №8»

6. Создать сквозной столбец с названиями магазинов.

Лабораторная работа № 9.

Упорядочение информации с помощью структур. Использование стилей.

Цель работы: Создать пятиуровневую структуру для нижеприведенной таблицы. Уметь использовать встроенные стили и создавать новые стили.

Функция структурирования служит для улучшения визуального контроля за данными в больших таблицах. Речь идет не столько об оформлении, а о разбиении информации по уровням структуры. С помощью отображения (скрытия) отдельных уровней структуры можно добиться представления на экране только необходимой информации. Разбиение по уровням структуры может происходить как в горизонтальном, так и в вертикальном направлении. Данные первого уровня не могут быть скрыты. Уровни структуры должны быть изначально заложены при построении таблицы. Благодаря использованию формул суммы, функция автоматического структурирования сама распознает уровни структуры.

Порядок выполнения работы: Создать таблицу 9.11. При заполнении столбца «Затраты» нужно очень внимательно вычислять суммы, т.к. любая ошибка приведет к созданию неверной структуры или структура не получится. Порядок вычисления сумм следующий: сначала подсчитывается сумма затрат за первый квартал (т.е. затраты за январь+ затраты за февраль+ затраты за март); аналогично определяется сумма за второй квартал; а сумма за первое полугодие определяется как сумма затрат за квартал1+сумма затрат за квартал2 (нельзя складывать затраты за 6 месяцев, только итоговые суммы по кварталам). По аналогии вычисляются суммы затрат за второе полугодие (как сумма затрат за квартал3+ сумма затрат за квартал4), а итоговые затраты за год представляют собой сумму затрат за первое полугодие+ сумма затрат за второе полугодие (нельзя складывать затраты за четыре квартала, только по полугодиям). Аналогично производятся вычисления для второго года, для получения общей суммы затрат за два года к итоговой сумме затрат за первый год прибавляется итоговая сумма затрат за второй год.

По аналогии рассчитывается количество привлекаемых сотрудников. Колонка число заполнятся информацией в формате «дата». С датами также можно производить простейшие вычисления. Предположим, что наш проект выполняется без учета выходных и праздничных дней. Тогда, чтобы вычислить количество рабочих дней, нужно от даты конца периода отнять дату начала периода и прибавить 1. Для квартала1 это будет выглядеть с.о.: 31.03.2007 – 01.01.2007, результат получится в формате «Дата», чтобы узнать количество дней вызываем Меню/Правка/Очистить форматы. Аналогично рассчитываются остальные периоды. Количество рабочих дней за 2 года: 31.12.2008-01.01.2007+1; и очистить форматы.

Таблица 9.11

Инжиниринг-технология услуг предпринимателю

Год	Квартал	Месяц	Этап инновационного цикла	Затраты	Привлекаемые контрагенты	Количество человек	Даты/ Кол-во дней
2010	квартал1	Январь	Технико-экономическое обоснование (ТЭО)	$ 1500	Эксперты	3	01.01.2007
2010	квартал 1	Февраль	Разработка бизнес плана	$ 1265	Экономисты	5	01.02.2007
2010	квартал 1	Март	Разработка бизнес плана	$ 1270	Эксперты	4	31.03.2007
2010	квартал 1	Итого за 1 кв	ТЭО+БП	? сумма1	Экономисты; эксперты	? сумма	? дней в 1кв.
2010	квартал 2	Апрель	Разработка проекта	$ 2300	Экономисты; эксперты	2	01.04.2007
2010	квартал 2	Май	Разработка проекта	$ 1800	Экономисты; эксперты	4	01.05.2007
2010	квартал 2	Июнь	Разработка проекта	$ 1200	Экономисты; эксперты	5	30.06.1007
2010	квартал 2	Итого за 2 кв	Разработка проекта	? сумма2	Экономисты; эксперты	? сумма	? дней во 2кв
2010	полугодие1	Итого за 1 полугодие	ТЭО+БП+РП	? сумма3= сумм1+сумм2	Экономисты; эксперты	? сумма	? дней в 1 полугодии
2010	квартал 3	Июль	Формирование команды	$ 1570	Эксперты	2	01.07.2007
2010	квартал 3	Август	Обучение специалистов	$ 2400	Преподаватели	2	01.08.2007
2010	квартал 3	Сентябрь	Разработка оборудования	$ 3110	Инженеры	2	30.09.2007
2010	квартал 3	Итого за 3 кв	Разработка оборудования	? сумма4	Эксперты Инженеры	? сумма	? дней в 3кв
2010	квартал 4	Октябрь	Изготовление оборудования	$ 3210	Инженеры; рабочие	4	01.10.2007
2010	квартал 4	Ноябрь	Изготовление оборудования	$ 3210	Инженеры; рабочие	4	01.11.2007
2010	квартал 4	Декабрь	Поставка оборудования	$ 2185	Инженеры	2	31.12.2007
2010	квартал 4	Итого за 4 кв	Поставка оборудования	? сумма5	Инженеры; рабочие	? сумма	? дней в 4кв
2010	полугодие2	Итого за 2 полугодие	Поставка оборудования	? сумма6= сумм4+сумм5	Инженеры; рабочие	? сумма	? дней в 2 полугодии
2010	1 год	Итого за 1год	Поставка оборудования	? сумма7= сумм3+сумм6	Эксперты Экономисты Инженеры; рабочие	? сумма	? дней в 1 году
2011	квартал 1	Январь	Пуско-наладка	$ 1999	Инженеры	3	01.01.2008
2011	квартал 1	Февраль	Испытание	$ 1735	Инженеры	3	01.02.2008
2011	квартал 1	Март	Сертификация	$ 1220	Инженеры	2	31.03.2008
2011	квартал 1	Итого за 1 кв	Пуско-наладка	? сумма8	Инженеры	? сумма	? дней в 1кв.
2011	квартал 2	Апрель	Сдача «под ключ»	$ 1750	Инженеры; рабочие	4	01.04.2008
2011	квартал 2	Май	Сервисное сопровождение	$ 500	Инженеры	3	01.05.2008
2011	квартал 2	Июнь	Сервисное сопровождение	$ 500	Инженеры	3	30.06.1008
2011	квартал 2	Итого за 2 кв	Сдача «под ключ»	? сумма9	Инженеры; рабочие	? сумма	? дней во 2кв
2011	полугодие1	Итого за 1 полугодие	Сдача «под ключ»	? сумма10= сумм8+сумм9	Инженеры; рабочие	? сумма	? дней в 1 полугодии
2011	квартал 3	Июль	Сервисное сопровождение	$ 500	Инженеры	3	01.07.2008
2011	квартал 3	Август	Сервисное сопровождение	$ 500	Инженеры	3	01.08.2008
2011	квартал 3	Сентябрь	Сервисное сопровождение	$ 500	Инженеры	2	30.09.2008
2011	квартал 3	Итого за 3 кв	Сервисное сопровождение	? сумма11	Инженеры	? сумма	? дней в 3кв
2011	квартал 4	Октябрь	Сервисное сопровождение	$ 500	Инженеры	2	01.10.2008
2011	квартал 4	Ноябрь	Сервисное сопровождение	$ 500	Инженеры	2	01.11.2008
2011	квартал 4	Декабрь	Сервисное сопровождение	$ 500	Инженеры	2	31.12.2008
2011	квартал 4	Итого за 4 кв	Сервисное сопровождение	? сумма12	Инженеры	? сумма	? дней в 4кв
2011	полугодие2	Итого за 2 полугодие	Сдача «под ключ»; сервис	? сумма13= сумм11+сумм12	Инженеры	? сумма	? дней в 2 полугодии
2011	2 год	Итого за 2год	Сдача «под ключ»	? сумма14= сумм10+сумм13	Инженеры	? сумма	? дней во 2 году
	За два года	Всего за 2года	от ТЭО до «под ключ»	? сумма15= сумм7+сумм14	Инженеры	? сумма	? дней за 2 года

1. Установить курсор в любом месте таблицы (активная клетка внутри таблицы).

2. Меню/Данные/Группа и Структура/Создание структуры.

3. Слева от названий столбцов и номеров строк появятся символы структуры (от 1.. .5) - это номера уровней структуры и линейки структуры. Можно задавать до 8 уровней структуры.

4. Нажимая на номера структуры (от большего к меньшему от № 5 к № 4). Обратите внимание, какая часть информации скрывается. Можно просмотреть более высокий уровень структуры. Дойти до 2, показать! Потом 1 уровень, потом снова 2.

5. С помощью крестика можно раскрыть отдельные группы структуры, с помощью "-" скрыть. Поупражняться. Показать результат. Получить индивидуальное задание.

Результат показать преподавателю и сохранить в свою папку.

4. Меню/ Данные / Группа и Структура / Удалить структуру.

5. Вручную построить вертикальную и горизонтальную структуры (получить задание у преподавателя). Меню/Данные/ Группа и Структура / Группировать. Показать результат.По частям удалить уровни структуры. Выделить отдельные группы структуры и выбрать команду Меню/Данные/Группа и структура/Разгруппировать.

6. Отформатировать таблицу с помощью любого автоформата. Показать. Отменить автоформат. Выделить таблицу Меню/Формат/Автоформат (выбрать в поле списка формат таблицы "нет") или отменить автоформат из Меню/Правка/Очистить/Форматы.

Для комбинации параметров форматирования для разных частей таблицы или для отдельных ячеек используются стили. По умолчанию мы работаем со стилем Обычный (normal). Перед тем как изменять стиль, ему необходимо задать новое имя, чтобы не запортить встроенные стандартные стили. В таблице выделить колонку "год". Меню/Формат/Стиль (style). Задать имя стиля Обычный_1. Удалить флажок "Ú" . Шрифт, нажать <изменить>. Выбираем вкладку Шрифт, полужирный курсив, 11 формат, вкладку "вид", цвет голубой, <ОК>, <Добавить> <ОК>. Показать.

Выделить столбец "квартал". Создать стиль "Обычный_2". Убираем "Ú". Рамка, <изменить>, цвет рамки на темно-синий, рамку задать слева, справа, сверху, снизу, стиль "—" <ОК>. Убираем "Ú". Узоры/<Вид> выбираем Узор ":::::", цвет светло-зеленый, <ОК>, <Добавить> <ОК>.

Выбираем колонку "месяц". Применить стиль "Обычный_2" к столбцу "месяц". Для столбца "этапы инновационного цикла" создадим стиль "Обычный_3", шрифт Times-New-Roman, 11, полужирный, цвет букв темно-синий.

Для столбца затраты выбрать стиль "денежный [0]". Задать новое имя, например: «Денежный_$». Единицы измерения рубли изменить на доллары и создать $ стиль. Ознакомиться с остальными стилями. Самостоятельно создать новые стили для "привлекаемых контрагентов" и "количество человек".

Результат показать преподавателю и сохранить в свою папку под именем лаба9_часть1 (под этим именем будет подразумеваться «старая» рабочая книга)

Создать новую рабочую книгу. Меню/Окно/Расположить рядом (по вертикали). На экране должно появиться 2 рабочие книги: ваша и новая - чистая.

Пометим в вашей рабочей книге столбец "месяцы" и скопируем его через Clipboard (Буфер) по адресу А2 в новую рабочую книгу, стиль тоже должен скопироваться.

Пометим в новой рабочей книге область В2:В41, пишем «=» в строке формул, и вернемся в старую рабочую книгу и пометим всех контрагентов, Затем нажмем "Ctrl Enter" одновременно. Обратим внимание на ссылку в строке формул: [имя книги], далее идет имя листа!, а затем интервал ячеек (так данные из одной рабочей книги ссылаются на данные из другой рабочей книги). По адресу С2 пишем ЗПл (Заработная плата). 200$, 202$ и т.д. с помощью функции автопродолжения (для данных применяется денежный форат). Для всех контрагентов введем заработную плату в новой рабочей книге. Месяцы должны быть оформлены в том же стиле, как и в старой рабочей книге. Но в новой рабочей книге у нас есть только стандартные стили, следовательно, их нужно взять из старой рабочей книги. В новой рабочей книге помечаем колонку месяцы. Стиль, объединить, вам предлагается имя старой рабочей книги (или его нужно выбрать из списка имен всех открытых рабочих книг).<ОК>, и выбираем нужные стили для всех столбцов новой рабочей книги.

Результат показать преподавателю и сохранить в свою папку под именем лаба9_часть2.

Построить объемную круговую диаграмму (для 1 квартала 2010года): Зависимость заработной платы от месяцев и контрагентов в новой рабочей книге.

Результат показать преподавателю и сохранить в свою папку.

Закрыть новую рабочую книгу. Вернуться к старой рабочей книге (часть1)

Построить кольцевую диаграмму затрат от времени по кварталам с подписями и названиями, в соответствии с таблицей 9.12

Таблица 9.12.

Затраты на создание проекта по кварталам

	2010 год	2011 год
1 кв.	=Адрес суммы затрат за 1 кв. 2010г	=Адрес суммы затрат за 1 кв. 2011г
2 кв.	=Адрес суммы затрат за 2 кв. 2010г	=Адрес суммы затрат за 2 кв. 2011г.
3 кв.	=Адрес суммы затрат за 3 кв. 2010г	=Адрес суммы затрат за 3 кв. 2011г
4 кв.	=Адрес суммы затрат за 4 кв. 2010г.	=Адрес суммы затрат за 4 кв. 2011г

Результат показать преподавателю и сохранить в свою папку.

Вопросы для самоконтроля.

1. Требования к автоматическому созданию структуры.

2. Как сгруппировать данные «вручную»?

3. Можно ли на экране увидеть несколько разных рабочих книг одновременно?

4. Как создать пользовательский стиль?

5. Как восстановить «запорченный» стандартный стиль в рабочей книге?

6. Можно ли построить круговую диаграмму для нескольких столбцов? Строк?

7. Для чего применяется кольцевая диаграмма?

Лабораторная работа №10.

Работа с массивами данных.

Цель работы: Освоение механизма работы с матрицами. Ознакомление с инженерными функциями.

Порядок выполнения работы:

Задание1

Пусть дана матрица 1, размером 5x5

По адресу А1 ввести «Матрица1» По адресам А2:Е6 разместить двумерный массив.

2 1 3 4 1

1 2 3 1 4

1 3 2 4 1

3 1 4 2 1

1 3 4 1 2

Каждое число помещать в свою ячейку. Для матрицы1 создать поименованную область: Матрица1

Найти транспонированную матрицу ТРАНСП(массив) с помощью «Мастера функций» (ссылки и массивы), выполнив следующие действия:

а) Выделяем пустую область, куда будет внесен ответ, также размером:5x5.

В строке формул пишем "=".

б) Вызываем мастер функций.

в) Находим функцию ТРАНСП.

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

д) Закончить.

е) Устанавливаем курсор в строке формул и нажимаем "Ctrl Shift Enter" одновременно. Аналогично найти обратную матрицу для массива1 с помощью МОБР();Выполнить проверку (произведение прямой и обратной матриц должны дать в результате единичную матрицу), также найти определитель, с помощью МОПРЕД();произведение двух матриц с помощью МУМНОЖ() (матрицу1*матрицу2).

Ввести матрицу2 Для нее тоже создать поименованную область: Матрица2.

Матрица 2:

3 1 5 4 2

1 2 1 3 4

4 5 1 3 2

4 3 2 1 5

2 1 3 5 4

Перемножить матрицы, используя имена областей.

Результат показать преподавателю и сохранить в свою папку.

Задание2

Построить сумму нарастающим итогом ,для а=2, n=5 и для а=4 и n=4.

Сумма нарастающим итогом часто используется в экономике, особенно в бухгалтерии. Сумма с нарастающим итогом вычисляется по шагам, сначала берется первое слагаемое- это будет первая (начальная) сумма; затем первое слагаемое складывается со вторым – это дает вторую сумму и т.д. В результате мы получаем ряд сумм и видим их постепенное изменение. Последняя сумма будет равняться общей сумме всех слагаемых ряда. Разложим приведенный пример в ряд Тейлора:

Для 1 случая:

Задание оформить следующим образом:

По адресу A1 ввести:

Таблица 10.13

	A	B	C	D	E	F
2	a=	2	n=	5
3	k	a^k	k!		сумма 1способ	сумма 2способ
4	1	=$b$2^A4	=фактр(A4)	=B4/C4	=D4	=Сумм($D$4:D4)
5	2	?	?	?	=E4+D5	(=Sum($D$4:D4))
6	3	?	?	?	?	?
7	4	?	?	?	?	?
8	5	?	?	?	?	?
9	Итого:			общая сумма
10	Проверка			=дельта(D9;E8)	=порог(D10;0)	=дельта(D9;F8)

С помощью функции «Дельта» производится проверка на равенство содержимого двух ячеек, если значения совпадают, то результат =1, иначе =0; если по адресам D10 и F10 получены 1, то все сделано верно. Функция «ПОРОГ» проверяет не превышает ли данное число пороговое значение. Если значение Е10=1, то результат верен.

По аналогии построить таблицу для варианта 2, где n=4 и a=4

Результат показать преподавателю и сохранить в свою папку.

Задание3

В мастере функций в разделе «Инженерные функции» есть часто используемые функции, такие как: работа с мнимыми числами, двойной факториал, функции перевода чисел из одной системы счисления в другую и многие другие. (Если каких-либо функций нет, то нужно включить Сервис/Надстройки:«Пакет Анализа»)

Перевести числа из одной системы в другую:

ВОСЬМ.В.ДВ (Oct2Bin) ; 71 ® ?

ВОСЬМ.В.ДЕС (Oct2Dec); 10 ® ?

ВОСЬМ.В.ШЕСТН (Oct2Hex);.13 ®?

ДВ.В.ВОСЬМ (Bin2Oct); 1000 ® ?

ДВ.В.ДЕС (Bin2Dec); 1001 ® ?

ДВ.В. ШЕСТН(Bin2Hex);1111 ® ?

ДЕС.В.ВОСЬМ(Dec2Oct);8 ® ?

ДЕС.В.ДВ(Dec2Bin); 12 ® ?

ДЕС.В.ШЕСТН(Dec2Hex);11 ® ?

ШЕСТН.В.ДВ(Hex2Bin); F ® ?

ШЕСТН.В.ДЕС (Hex2Dec); A ® ?

ШЕСТН.В.ВОСЬМ(Hex2Oct) 9 ® ?

Результат показать преподавателю и сохранить в свою папку.

Вопросы для самоконтроля.

1. В чем различие при записи результата вычислений в область с помощью "Ctrl Enter" и "Ctrl Shift Enter"

2. В чем заключается работа функции «ПОРОГ»?

3. В чем заключается работа функции «ДЕЛЬТА»?

4. Получить вариант у преподавателя. Построить таблицу по заданию 2

для n=6 а=3

5. Функции перевода чисел из одной системы счисления в другую.

Лабораторная работа №11.

Построение прогнозов и трендов.

Цель работы: Освоение методики построения прогнозов с помощью статистических функций: РОСТ; ПРЕДСКАЗ; ТЕНДЕНЦИЯ. Построение линий трендов на графиках. .

Порядок выполнения работы: Часто при решении экономических задач требуется ответить на вопрос: «Как поведет себя исследуемая (зависимая) переменная величина, если факторы, воздействующие на нее (независимые переменные), будут изменяться каким-либо образом?» Т.е. нужно владеть методикой прогнозирования и уметь оценить достоверность результата. На листе1 заполнить таблицу 11.14 в соответствии с формулами: Для этого в Excel предусмотрены специальные статистические функции: РОСТ; ПРЕДСКАЗ; ТЕНДЕНЦИЯ, ЛГРФПРИБЛ, ЛИНЕЙН.

По адресам D2:D11 рассчитать Y₁=a*Z^X2*b, для а=1; b=5; Z=2; X2=(B2:B11), т.е. X2 не является константой, а значения X2 изменяются в интервале от =(B2:B11).

По адресам F2:F11 занести формулу: Y₂=c*d^X1 , где с=4; d=2, а значения X1=(А2:А11) лежат в интервале от А2 до А11.

Таблица 11.14

	A	B	C	D	E	F	G	H
1	x1	x2		Y1 факт		Y2 факт	Y2 рассч1	Y2 рассч2
2	1	2	16	?	7,1264	?	?	?
3	2	4	32	?	7,4669	?	?	?
4	3	6	48	?	7,6883	?	?	?
5	4	8	64	?	7,8912	?	?	?
6	5	10	80	?	7,6218	?	?	?
7	6	12	96	?	7,8813	?	?	?
8	7	14	112	?	8,1317	?	?	?
9	8	16	128	?	8,3479	?	?	?
10	9	18	144	?	8,3836	?	?	?
11	10	20	160	?	8,3861	?	?	?
12	11	22			8,6269		?	?
13	12	24					?	?
14	13	26					?	?
15
16	a=	1
17	b=	5
18	z=	2
19	c=	4
20	d=	2
21

Задание 1

Построить прогноз с помощью функции РОСТ (GROWTH), которая вызывается с помощью мастера функций, из категории «Статистические». Функция РОСТ возвращает значение в соответствии с экспоненциальным трендом.( y = b*m^x.) Рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных. Функция РОСТ возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений. Функция рабочего листа РОСТ может применяться также для аппроксимации существующих x- и y-значений экспоненциальной кривой. (Т.е. она аппроксимирует с помощью экспериментальной кривой известные значения x и y и возвращает соответствующие этой кривой новые значения y расчетное, которые соответствуют новым значениям х.) РОСТ(известные_значения_y;известные_значения_x;новые_значения_x;конст)

Если константа=1 (логическая), то значение b не рассчитывается, а принимается за 1.

Порядок вычислений. 1 способ.

1. Пометить область результата: (G2:G14)

2. Вызвать окно мастера функций

3. В статистических функциях выбрать функцию РОСТ «ОК»

4. Ввести известные значения у2(факт) = $F$2:$F$11

5. Ввести известные значения x1(факт) = $A$2:$A$11

6. Ввести новые значения x1 = A2:A14 (без знаков абсолютной ссылки, обратите внимание, что мы будем рассчитывать новые значения у расчетное для всех х факт+новые значения независимой переменной)

7. «ОК»

8. Вернуться в строку формул и нажать «Ctrl Enter»

Результат показать преподавателю и сохранить в свою папку.

2 способ.

1. Пометить область результата: (H2:H14)

2. Вызвать окно мастера функций

3. В статистических функциях выбрать функцию РОСТ «ОК»

4. Ввести известные значения у2(факт) = F2:F11

5. Ввести известные значения x1(факт) = A2:A11

6. Ввести новые значения x1 = A2:A14

7. «ОК»

8. Вернуться в строку формул и нажать «Ctrl Shift Enter»

Задание 2

Построить прогноз с помощью функции ПРЕДСКАЗ (FORECAST), которая вызывается с помощью мастера функций, из категории «Статистические». Функция ПРЕДСКАЗ возвращает значение функции в точке х, предсказанное на основе линейной регрессии для массивов известных значений х и у. Эту функцию можно использовать для предсказаний будущих продаж, потребностей в оборудовании или тенденций потребления.

. Синтаксис: ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)

x— это точка данных, для которой предсказывается значение.

Известные_значения_y— это зависимый от х массив или интервал данных.

Известные_значения_x— это независимый массив или интервал данных.

Скопируем на лист 2 область А2:В14 из листа1 По адресу С1 напишем у факт; по С2 разместим формулу у=ах+b и протянем ее до С12. По адресу А16 занесем "а=", (это константа "а"), адресу В16 занесем 8; по адресу А17 занесем "b=", по В17 занесем 24 - это константа "b", по В1 напишем х; х или независимая переменная займет область (В2:В14).

1. Пометим область (D2: D14).

2. Запишем =.

3. Вызовем мастер функций, ПРЕДСКАЗ.

4. Пометим значения х, для которых строится предсказание (В2:В14).

5. Введем значения у=($С$2:$С$12).

6. Введем известные х=($В$2:$В$12).

7. «ОК».

8. Перейдем в строку формул.

9. "Ctrl Enter".

То же для массива по Е2:Е14, "Ctrl Shift Enter". Проверим результат расчетов, сравнив его с тем, который получится по адресу С13:С14 при копировании формулы.

Самостоятельно ознакомиться с функцией ТЕНДЕНЦИЯ, Построить значения у с помощью этой функции по адресам F2:F14, G2:G14.

Задание 3

На листе 3 набрать по А1 «х1»; по В1 «х2»; по С1 «y1»; по D1 «y2»; далее вправо: «Рост1»; «Предсказ1»; «Тенденция1»; «Рост2»; «Предсказ2»; «Тенденция2». На лист 3 из листа1 скопировать А2:В14, по С2 скопировать (из листа1) С2:С11, no D2 скопировать из (листа1) Е2:Е12.

Построить все три вида прогнозов для х1=А2:А11 и у₁=С2:С11; для х2=В2:В12 и у₂=Е2:Е12 в результате должно получиться 6 функций.

Выбрать наилучший результат. Показать исходные и прогнозные функции на графиках (в результате получим 2 графика по 4 функции). Построить линии "Тренда". Для этого выделяем фактическую кривую и с помощью правой кнопки мыши вызываем контекстное меню, где выбираем пункт «Добавить линию тренда», в диалоговом окне выбираем вид функции и указываем, на сколько периодов строим прогноз, также ставим галочки, чтобы на графике появилось уравнение линии тренда и достоверность аппроксимации. Построить по 3 линии тренда для каждого графика и оценить результат.

Результат показать преподавателю и сохранить в свою папку.

Коэффициенты для аппроксимирующих функций можно определить с помощью функций ЛГРФПРИБЛ, ЛИНЕЙН.

Вопросы для самоконтроля.

1. В чем различие при записи результата вычислений в область с помощью "Ctrl Enter" и "Ctrl Shift Enter"

2. Какие функции применяются для построения прогнозов?

3. Как построить линии тренда?

4. Что показывает R²?

5. С помощью функции ЛИНЕЙН() рассчитать коэффициенты m и b (результаты занести по адресам Е3:F7 <Ctrl Shift Enter>) и расчетные значения прогнозной прямой; выполнить проверку

месяц	продажи	y=mx+b	y=mx+b	ЛИНЕЙН
Xfact	Yfact	Yрассч		m	b
1	3100	3000	коэффициенты	1000	2000
2	4500	4000	станд.ош коэф.	133,0950251	518,3306538
3	4400	5000	r^2 коэф.детерминир (1)	0,933831377	556,7764363
4	5400	6000	ст.ош у	56,4516129	4
5	7500	7000	регр. Сумма квадратов и остаточная сумма квадратов	17500000	1240000
6	8100	8000		#Н/Д	#Н/Д
7		9000		#Н/Д	#Н/Д
8		10000
9		11000
10		12000
ПРОВЕРКА:
За 10-й месяц		12000
		12000

месяц	продажи	y=mx+b	y=mx+b	ЛИНЕЙН
Xfact	Yfact	Yрассч		m	b
1	3100	=$E$3*A3+$F$3	коэффициенты	1000	2000

10		12000
ПРОВЕРКА:
За 10 месяц		=СУММ(ЛИНЕЙН(B3:B8;A3:A8)*{10;1})
		12000

Лабораторная работа № 12.

Основы работы со списками данных.

Цель работы: построение таблицы базы данных, сортировка записей, фильтрация, работа со статистическими функциями баз данных.

Для размещения больших объемов информации в связанном виде используются базы данных (Б.Д.). Б.Д. - это средство для организации и управления большими объемами информации. Реляционная БД обычно представлена в виде двумерных таблиц.

Первая строка Б.Д. содержит заголовки столбцов - имена полей. Под именами полей располагаются сами поля. Остальные строки - записи.

Введем заголовок базы данных по А1, по A3, ВЗ,... будем вводить строку с именами полей.

A3 -таб. номер (табельный номер), ВЗ- имя, СЗ фамилия, DЗ страна, ЕЗ город, F3 фирма, G3 образование, НЗ стаж, I3 категория, J3 ставка, КЗ зарплата.

Начиная с А4 введем первую запись:

1001 Иван Романеков Россия С-Пб IBM Инженер 13 4 $ 200,0 в поле З.П. введем формулу =Ставка * категорию * стажи * 0,2. К полю ставка применим один из денежных форматов. Затем красиво отформатируем строку заголовка, имена полей и первую запись (можно использовать разные стили, шрифт, цвета и т.д.)

Затем пометим строку с именами полей и строку с первой записью и выберем Меню/Данные (data)/Форма (form). Мы видим на экране Форму, которая содержит данные первой записи и ряд кнопок справа. Над кнопками мы видим номер текущей записи и сколько всего записей в списке.

При щелчке по кнопке Добавить (создать) (new) Excel высвечивает новую форму данных: новая запись (new record), которую мы будем заполнять. (Не нажимать ВВОД!!!).

После ввода значения поля нажать «Tab». В вычисляемое поле ничего не вводим! В нем значения должны вычисляться автоматически. И оно должно быть недоступно. Самостоятельно ввести записи из табл. 12.15

Когда запись введена можно нажать: "Добавить" (NEW). Если нужно отредактировать предыдущее значение, нажать "Shift Tab", если хотим в поле скопировать информацию из предыдущей записи нажать "Ctrl "". По завершению работы нажать ("close") "закрыть" или "Esc", после чего сохранить рабочую таблицу командой (Save) "сохранить".

Таблица 12.15

A	B	C	D	E	F	G	H	I	J	K
Таб№	Имя	Фамилия	Страна	Город	Фирма	Образование	Стаж	Катего-рия	Ставка	зарплата
1002	Елена	Кузнецова	США	Нью-Йорк	IBM	инженер	5	12	210
1003	Игорь	Рубин	США	Нью-Йорк	Intel	магистр	5	14	220
1004	Илья	Казачук	Россия	С-Пб	Intel	магистр	5	15	215
1005	Андрей	Гетманский	Франция	Париж	IBM	инженер	6	13	200
1006	Денис	Колесников	Англия	Лондон	Microsoft	магистр	4	14	225
1007	Дмитрий	Зуев	Россия	Москва	Microsoft	бакалавр	6	11	200
1008	Дмитрий	Кузькин	США	Нью-Йорк	Microsoft	дтн	3	16	250
1009	Евгения	Алейникова	Россия	Москва	IBM	магистр	4	14	228
1010	Ольга	Александрова	Россия	С-Пб	Microsoft	инженер	5	13	205
1011	Юлия	Беляева	Россия	С-Пб	Intel	магистр	5	11	190
1012	Елизавета	Демьянова	США	Нью-Йорк	Microsoft	инженер	4	12	200
1013	Сергей	Иванов	Канада	Оттава	Intel	инженер	4	13	210
1014	Роман	Морозов	Россия	С-Пб	Microsoft	инженер	5	13	205

Таблица начинается с 1002 записи, т.к. 1001 запись должна быть уже введена для создания формы.

Работа со списками данных.

Задание1. Поиск, изменение и удаление записей с помощью формы.

Перед редактированием Б.Д. выделяем всю базу с именами полей, набрать Меню/Данные/Форма. С помощью "Enter", «¯» или кнопки «Далее»(Find Next) или с помощью полос. пр_0Крутки просматриваем все записи Пусть 0льга Александрова сменила фамилию на Иванову и уехала в Москву найдем нужную запись и внесем изменения в соответствующие поля (обратите внимание, что такие же изменения произошли и на рабочем листе.) Для быстрого перемещения по БД используются следующие клавиши:

"Page. Dn." / "Page. Up." - вниз/вверх на 10 записей.

"Ctrl Pg. Up."; "Ctrl ­ - переход к 1-й записи.

"Ctrl Pg. Dn."; «Ctrl ¯" - переход к последней записи.

Чтобы очистить поле: нужно его выделить и нажать «Del». Переход к предыдущей записи осуществляется с помощью: «Назад» (Previous), чтобы удалить запись нужно нажать «удалить» (Delete), причем «Отменить»(UNDO) - эту запись не вернет.

Чтобы не искать нужную запись вручную, используем кнопку «Критерий»(Criteria). Например: нужно найти сотрудников, у которых Стаж > 4 лет из компаний на I* (можно использовать знаки ??? вместо символов поля). При задании критерия поиска можно использовать следующие знаки: =; >; <; <=; >=; <>(не равно) Проверить, сколько записей удовлетворяют данному условию. Закрыть форму.

Задание2. Сортировка записей.

Мы можем задавать (IF) до трех полей, по которым идет сортировка (т.е. до 3-х уровней сортировки, так называемые вложенные сортировки). Установить курсор в первое имя поля или в любое поле Б.Д. Меню/Данные/Сортировка, в окне «Сортировка диапазона» выбрать из списка. «Сортировать по» (Sort By) и задать имя поля, по которому нужно отсортировать записи, а также порядок сортировки «По возрастанию»(Ascending), т.е. «по алфавиту» или «По убыванию» (descending).Если внутри отсортированного диапазона требуются дополнительные сортировки, тогда выбираем: «Затем по» (Then by) и еще раз. «Затем по» (Then by)( или «В последнюю очередь, по») (отменить сортировку можно: Edit/Undo Sort)

Исходная база отсортирована по табельным номерам. .Скопировать БД вниз и эту БД отсортировать:

По странам по возрастанию

Затем по: фирмам по убыванию

Затем по: городам по возрастанию

Нажать «ОК» или «Enter»

Еще раз скопировать БД вниз и самостоятельно отсортировать: по фирмам (по убыванию); по странам (по возрастанию); по фамилиям (по убыванию). Для сортировки также можно использовать элементы Сортировка по Возрастанию/Убыванию в стандартной панели инструментов

Результат показать преподавателю и сохранить в свою папку.

Задание3. Фильтрация данных. Для выполнения задания выбираем первую БД.

Фильтрация данных используется для того, чтобы спрятать ненужные записи Меню/Данные/Фильтр/Авто фильтр (Перед этой командой курсор в одной из клеток с именем поля). В каждой клетке с именем поля появится раскрывающийся список. Теперь выберем в списке с именем поля "Город" "Москва". Затем скопируем полученную базу данных вниз. Затем отменим фильтрацию: Меню/ Data (данные)/Фильтр (Filter) / Все (Show All) (показать все) или в раскрывшемся списке поля, по которому производилась фильтрация указать «Все» (после каждого задания необходимо восстанавливать первую БД)

Затем отфильтровать всех работающих в фирме INTEL. Скопировать результат вниз, на новое место. Из этого списка выбрать тех, кто работает в США(также с применением автофильтра). Копировать, восстановить.

Нужно отфильтровать информацию о людях, имена которых начинаются на буквы «Е» или «И».

Чтобы создать свой авто фильтр, идем в Меню/Данные/Фильтр/Авто фильтр, в поле имя выбираем опцию «условие» (custom). Появится диалоговое окно «Пользовательский авто фильтр» (custom auto filter). Зададим оператор сравнения «равно» (=), затем в окне справа зададим Е*; ИЛИ (поставить точку<•>) равно И* <ОК>. Скопируем, восстановим Б.Д. Теперь отфильтруем с помощью автофильтра всех служащих, имена которых начинаются на букву "Е*", кроме (не равно(<>)) Елена с помощью логической операции AND. Что получилось?

Теперь отфильтруем тех, кто находится в стране: США и стаж, у которых: >= 4 лет и <= 5 лет.

При использовании расширенного, (усиленного) фильтра, критерии фильтрации можно задавать в рабочем листе. Для задания условий выборки имеются более широкие возможности.

П

С помощью этого диапазона условий или области критериев можно отфильтровать записи о тех, кто работает в России или США (тех и других)

од базой данных на свободном месте, скопируем строку с именами полей. Под именем поля "страна" пишем: Россия ИЛИ США

Страна

Россия

США

Если поля располагаются друг под другом, то работает логическая операция ИЛИ, если же поля располагаются рядом, в одной строке, то работает логическая операция И.

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

Таб№	Имя	Фамилия	Страна	Город	Фирма	Образование	Стаж	Катего-рия	Ставка	зарплата	Катего-ри
			Россия					>12	>=4		<16
			США					>12	>=4		<16

Для поля «Категория» работает логическая операция «И»

Меню /Данные /Фильтр /Расширенный фильтр. В открывшемся окне выбрать опцию «скопировать результат в другое место», затем указать исходный диапазон (интервал списка) - это адреса, где располагается вся Б.Д. с именами полей. Указать «Диапазон условий»или «область критериев». В нашем случае диапазон критериев займет три строки. Далее «Поместить результат в диапазон», где указать адрес левой верхней ячейки , где будут располагаться отфильтрованные данные «ОК». Произвести выбор записей с помощью расширенного фильтра, показать результат

Результат показать преподавателю и сохранить в свою папку

Задание4. Знакомство со статистическими функциями Б.Д.

Для работы с Б.Д. имеются специальные функции категории «Работа с базой данных», которые в общем случае имеют вид: функция (Б.Д.; поле; критерий). Б.Д. - адрес Б.Д.с именами полей; Поле -имя поля в "БД" или адрес имени поля, критерий - адрес области критерия. Если в области критериев в полях не указать никаких условий, то счет производится по всей Б.Д.

1. БСЧЕТ (Б.Д., поле, критерий) – определяет количество ячеек, содержащих числа. Определить с помощью функции БСЧЕТ сколько человек получило ставку >200 и <=220?

Сначала создаем область критериев или диапазон условий. Поле «Ставка» повторяется дважды, т.к. присутствует логическая операция «И», затем устанавливаем курсор вне области критериев (ниже через строку) и вызываем «Мастер функций», выбираем Функцию «БСЧЕТ», указываем исходную БД, Имя поля, для которого ведется расчет, и выделяем диапазон условий «ОК».

2. ДМАКС (DMAX) (Б.Д., поле, критерий)-вычисляет max число в поле.. Найти максимальные и минимальные зарплаты для всех служащих, удовлетворяющее критерию "для всех"

3 ДМИН (DMIN) (Б.Д., поле, критерий) - min число в поле..

4 ДCP3HAЧ (DAVERAGE) (Б.Д., поле, критерий) - среднее значение поля.

Найти среднюю зарплату для служащих в США.

5. БДСУММ (Б.Д., имя поля, критерий) (DSUM)- сумма значений поля в соответствии с критерием. Найти суммарную зарплату для служащих в России.

6. БДПРОИЗВЕД (Б.Д., имя поля, критерий) произведение значащих клеток в соответствии с критерием. Найти произведение заработных плат для служащих в России.

7. БДДИСПП (Б.Д., имя поля, критерий) - дисперсия значений. Найти дисперсию значений для поля "стаж" для всех.

8. ДСТАНДОТКЛП (Б.Д., имя поля, критерий) - стандартное отклонение, если Б.Д. описана как генеральная совокупность. Подсчитать стандартное отклонение для поля "стаж" для всех.

Проверить результаты вычислений: извлечь квадратный корень из дисперсии, полученное значение должно быть равно стандартному отклонению.

Результат показать преподавателю и сохранить в свою папку

Вопросы для самоконтроля.

1. Как задать критерии в форме?

2. Как расположить записи по возрастанию/убыванию по выбранному имени поля?

3. С помощью какого инструмента можно фильтровать данные?

4. Что такое фильтрация?

5. Как создать пользовательский фильтр?

6. Как создать расширенный фильтр?

7. Как применить логические операции при создании диапазона условий? Какие?

8. В чем особенность работы со статистическими функциями БД?

Лабораторная работа № 13

Логические функции

Цель работы: Изучение работы логических функций и конструкций "Если". Решение задач с условиями.

В Excel встроены шесть логических функций

1. ЕСЛИ

Синтаксис: ЕСЛИ (лог_выражение; значение1_если_истина;

Значение2_если_ложь)

Результат: Возвращает значение1, если логическое выражение при вычислении дает значение ИСТИНА, и значение2, если логическое выражение ЛОЖЬ. Функция ЕСЛИ имеет две синтаксические формы. Функция ЕСЛИ используется для проверки значений формул и организации переходов в зависимости от результатов этой проверки. Результат проверки определяет значение, возвращаемое функцией ЕСЛИ.

, Аргументы: Лог-выражение: любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина: значение, которое возвращается, если логвыражение имеет значение Истина. Если лог-выражение имеет значение ИСТИНА и значение1еслиистина опущено, то возвращается значение ИСТИНА.

Значение_если_ложь: значение, которое возвращается, если логвыражение имеет значение ЛОЖЬ. Если лог_выражение имеет значение ЛОЖЬ и значение_если_ложь опущено, то возвращается значение ЛОЖЬ.

2. И

Синтаксис: И(логическое_значение1; логическое_значение2; ...)

Результат: Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

з.или

Синтаксис: ИЛИ (логическоезначение 1; логическое _значение2;...) Результат: Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.