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

Для оформления больших таблиц часто требуется, чтобы заголовки повторялись. Конечно, можно применить колонтитулы (Меню/Вид/Колонтитулы или в параметрах страницы). Но есть другой способ. На одном рабочем листе может находиться несколько печатных страниц. Размещение информации по страницам можно увидеть с помощью Меню/Сервис/Параметры, вкладка «Вид», раздел «Параметры окна», поставить галочку «авторазбиение на страницы». На листе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.2011 – 01.01.2011+1, результат получится в формате «Дата», чтобы узнать количество дней вызываем Меню/Правка/Очистить форматы. Аналогично рассчитываются остальные периоды. Количество рабочих дней за 2 года: 31.12.2012-01.01.2011+1; и очистить форматы.

Таблица 9.11

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

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

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

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

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

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

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

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

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

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

Для комбинации параметров форматирования для разных частей таблицы или для отдельных ячеек используются стили. По умолчанию мы работаем со стилем Обычный (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 квартала 2011года): Зависимость заработной платы от месяцев и контрагентов в новой рабочей книге.

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

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

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

Таблица 9.12.

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

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

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

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
	a=		n=
	k	ak	k!		сумма 1способ	сумма 2способ
		=$b$2^A4	=фактр(A4)	=B4/C4	=D4	=Сумм($D$4:D4)
		?	?	?	=E4+D5	(=Sum($D$4:D4))
		?	?	?	?	?
		?	?	?	?	?
		?	?	?	?	?
	Итого:			общая сумма
	Проверка			=дельта(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. Функции перевода чисел из одной системы счисления в другую.

6. Что получится в результате перемножения исходной матрицы на обратную?.

Лабораторная работа №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

Задание 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>) и расчетные значения прогнозной прямой; выполнить проверку

Лабораторная работа № 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