1. Создание исходной таблицы

1.1. Создание заголовка таблицы

Выделим диапазон ячеек A1:G1. Из контекстного меню выберем пункт Формат ячеек. В открывшемся одноименном окне перейдем на вкладку Выравнивание.

Зададим выравнивание по центру по горизонтали и по вертикали, а также установим флажок Объединение ячеек.

Для объединенного интервала A1:G1 зададим полужирное начертание и 9 размер.

1.2. Создание заголовков столбцов таблицы

В объединение ячеек А3:А4 введем заголовок первого столбца Секторы деятельности.

В каждую из ячеек B4. D4 и F4 введем текст «млн. руб.», а в каждую из ячеек C4, E4 и G4 – «в % к итогу».

В три объединения ячеек В3:С3, D3:E3 и F3:G3 введем года 1998, 1999 и 2000 соответственно.

В ячейки А5 – G5 введем номера столбцов с 1 по 7.

1.3. Заполнение таблицы исходными данными

В ячейки А7 – А12 введем следующие названия секторов деятельности: Государственный, Предпринимательский, Высшего образования и Частный бесприбыльный, а также Максимальные затраты и Средние затраты.

В ячейки В7 – В10, D7 – D10 и F7 – F10 занесем исходные данные по каждому из секторов деятельности за каждый год.

1.4. Вид окна после ввода исходных данных

После заполнения таблицы исходными данными, как было описано выше, окно Microsoft Excel будет иметь вид:

2. Выполнение расчетов в таблице

2.1. Вычисление итоговых сумм по годам

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

Перейдем в ячейку В6, нажмем кнопку Автосумма на панели инструментов.

В качестве диапазона суммирования зададим диапазон ячеек В7:В10.

Аналогичным способом рассчитаем итоговые значения за 1999 и 2000 гг., поместим результаты в ячейки D6 и F6 соответственно.

2.2. Вычисление процентов к итогу

Рассмотрим вычисление процентных соотношений по секторам деятельности за 1998 г.

Так как суммарное значение за год составляет 100%, то в ячейку С6 введем 100.

Для расчета процента по каждому сектору деятельности нужно  сумму по данному сектору  деятельности нужно делить на общую сумму и умножать на 100.

Например, для расчета процента по Государственному сектору деятельности нужно в ячейку В7 ввести формулу: «=B6/B6*100».

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

2.3. Вычисление средних и максимальных значений по каждому году

Для примера рассмотрим вычисления максимального и среднего значений за 1998 г.

Перейдем в ячейку В11. Нажмем стрелочку около кнопки Автосумма и выберем функцию МАКС.. В качестве диапазона зададим В7:В10.

В ячейке В12 будем рассчитывать среднее значение за все секторы деятельности за 1998 г. Для этого в ячейку введем функцию Среднее, а диапазон зададим В7:В10.

Аналогичным образом вычислим средние затраты за 1999 и 2000 гг.

2.4. Вид окна Excel после ввода всех формул

Для просмотра всех формул выберем пункт главного меню Сервис>Зависимости формул>Режим проверки формул.

После этого окно Microsoft Excel будет иметь вид:

2.5. Вид таблицы, после выполнения всех расчетов

После проведения всех расчетов, таблица будет иметь вид:

3. Использование расширенного фильтра для отбора данных

Перед тем, как использовать Расширенный фильтр нужно в некотором диапазоне задать все необходимые условия.

Фильтрацию будем выполнять на новом листе книги, в который скопируем исходную таблицу, скрыв строку Всего, в том числе:

 Над существующими заголовками таблицы добавим 4 пустые строки. Скопируем номера столбцов из диапазона A9:G9 в диапазон A5:G5.

3.1. Список секторов деятельности с затратами на исследование в 1999 г., превышающими 50%  от итоговых

В ячейку Е3 введем необходимое условие >50.

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

Выделим диапазон ячеек A7:G15.

Выполним пункт главного меню Microsoft Excel Данные > Фильтр > Расширенный фильтр.

В открывшемся одноименном окне в качестве исходного диапазона зададим диапазон А9:G15, в котором записаны данные по секторам деятельности а в качестве диапазона условий – диапазон A2:G3, в которой записано условие «>50».

Также установим переключатель Фильтровать список на месте.

После этого окно установок расширенного фильтра примет вид:

После применения расширенного фильтра получим следующие данные о секторах деятельности с затратами на исследование в 1999 г. более 50%:

3.2. Список секторов деятельности с затратами на исследование в 2000 г. от 1,5% до 20%

Этот фильтр создадим аналогично, описанному выше.

В ячейку G3 введем условие >=1,5, а в ячейку G4 - <=20/

Выполним пункт главного меню Microsoft Excel Данные > Фильтр > Расширенный фильтр.

В открывшемся одноименном окне в качестве исходного диапазона зададим диапазон А12:G15, в котором записаны данные по секторам деятельности а в качестве диапазона условий – диапазон G3:G4, в которой записаны условия «>=1,5» и «<=20».

Также установим переключатель Фильтровать список на месте.

В качестве исходного диапазона зададим диапазоны G12:G15.

В качестве диапазона условий зададим G3:G4.

В результате применения фильтра получим данные о секторе, в котором затраты на исследование составили в 2000 г. от 1,5% до 20%:

3.3. Определение общей суммы затрат на исследование в предпринимательским и частном секторах деятельности

В ячейку H4 введем дополнительный заголовок Общая сумма.

В ячейку Н9 введем формулу: «=B9+D9+F9», которая будет рассчитывать общую сумму затрат на исследование в предпринимательской деятельности.

В ячейку Н11 введем формулу «=B11+D11+F11», которая будет вычислять сумму в частной деятельности.

В результате таблица примет вид: