Сортировка, фильтры и промежуточные итоги

В табличном процессоре Microsoft Excel команды сортировки, фильтрации и промежуточных итогов находятся на вкладке ленты Данные (рис. 49).

Рис. 49. Вкладка ленты Данные

Рассмотрим пример обработки данных:

1. В табличном процессоре создайте таблицу (рис 50).

Рис. 50. Исходные данные

2. Для столбца Дата поступления установите формат ячеек – Дата, для столбцов Цена и Стоимость – Денежный формат.

3. Отсортируйте таблицу по столбцу Наименование товара, а затем по дате поступления. Для этого:

• выделите диапазон ячеек C4:G19;

• выполните команду: вкладка ленты Данные ► панель инструментов Сортировка и фильтр ► кнопка Сортировка;

• выберите сортировать по Наименованию товара, затем добавьте новый уровень сортировки по Дате поступления (рис. 51).

Рис. 51. Сортировка

4. Переименуйте Лист 1 в Сортировка.

5. Скопируйте таблицу на Лист 2, который переименуйте в Итоги.

6. Подведем промежуточные итоги:

• выделите диапазон B4:G19;

• выполните команду: вкладка ленты Данные ► панель инструментов Структура ► кнопка ;

• в появившемся диалоговом окне укажите операцию суммирования по столбцу Стоимость и нажмите кнопку ОК (рис. 52).

7. После выполнения команды подведутся промежуточные итоги (рис. 53).

Рис. 52. Подведение итогов

Рис. 53. Промежуточные итоги

8. Лист 3 переименуйте в Фильтр. Скопируйте на него исходную таблицу.

9. Для включения фильтра выделите диапазон данных и выполните команду: вкладка ленты Данные ► панель инструментов Сортировка и фильтр ► кнопка .

10. После выполнения команды возле заголовков появятся кнопки фильтра .

11. Отфильтруйте товары, поступившие в 2009 году с ценой от 3 000 до 20 000 руб.

12. Для отбора товаров поступивших в этом году нажмите на кнопку и в раскрывающемся списке выберите команду Фильтры по дате ► укажите 2009 год.

13. Аналогичным образом отберите товары с ценой от 3 000 до 20 000 руб.

14. После выполнения фильтров в таблице останутся следующие данные:

Рис. 54. Фильтрация данных

Сводные таблицы

Сводные таблицы применяются для группировки, обобщения и анализа данных, находящихся в списках Microsoft Excel.

Рассмотрим пример:

1. В Microsoft Excel 2010 оформите таблицу (рис. 55).

Рис. 55. Исходные данные

2. Перейдите на Лист 2.

3. Выполните команду: вкладка ленты Вставка ► панель инструментов Таблицы ► кнопка .

4. Укажите диапазон ячеек Лист1!$A$3:$C$16 и нажмите кнопку ОК.

5. Выберите поля Исполнитель и Стоимость работ (рис. 56).

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

6. Измените заголовки в сводной таблице (рис. 57).

Рис. 57. Сводная таблица

7. На основе сводной таблицы постройте сводную диаграмму (рис. 58).

Рис. 58. Сводная диаграмма

Решение систем линейных уравнений

В табличном процессоре Microsoft Excel имеется возможность решать системы линейных уравнений различными способами.

Решение систем линейных уравнений методом Крамера

Пусть задана система линейных уравнений

Неизвестные x₁, x₂, … , x_n вычисляются по формулам:

 – определитель матрицы А,

_i – определитель матрица, полученный из матрицы А путем замены i-го столбца вектором b.

, , , , .

Рассмотрим пример: решить систему линейных уравнений методом Крамера.

Запишем в табличном процессоре Microsoft Office Excel 2007 матрицы, которые понадобятся нам при вычислениях (рис. 59).

Рис. 59. Исходные данные

Найдем определители , ₁, ₂, и ₃, используя математическую функцию МОПРЕД (рис. 60).

Рис. 60. Вычисление определителей

Корни уравнения найдем по формулам:

В результате всех вычислений должны получиться следующие данные:

Рис. 61. Вычисление корней системы уравнений

Решение систем линейных уравнений с помощью обратной матрицы

Пусть дана система линейных уравнений

Эту систему можно представить в матричном виде: А·Х=В, где

, , .

Умножим систему линейных алгебраических уравнений А·Х=В слева на матрицу, обратную к А. Тогда система уравнений примет вид:

А^-1·А·Х=А^-1·В.

Так как А^-1·А=Е (единичная матрица), то получим Е·Х=А^-1·В.

Таким образом, вектор неизвестных вычисляется по формуле: Х=А^-1·В.

Рассмотрим пример: решить систему линейных уравнений с помощью обратной матрицы.

Запишем в табличном процессоре матрицу А и столбец свободных членов В (рис. 62).

Рис. 62. Исходные данные

Нам необходимо найти обратную матрицу А^-1, для этого:

1. выделите диапазон ячеек В8:D10;

2. вызовите функцию МОБР;

3. в появившемся диалоговом окне заполните поле ввода Матрица. Это поле должно содержать диапазон ячеек, в котором хранится исходная матрица, то есть В2:D4, нажмите кнопку ОК;

4. В первой ячейке выделенного диапазона появиться некоторое число. Чтобы получить всю обратную матрицу, необходимо нажать клавишу F2, для перехода в режим редактирования, а затем одновременно клавиши Ctrl+Shift+Enter (рис. 63).

Рис. 63. Обратная матрица

Осталось найти вектор неизвестных по формуле Х=А^-1·В, для этого:

1. выделите диапазон ячеек G8:G10;

2. вызовите функцию МУМНОЖ;

3. в поле для первой матрицы укажите диапазон В8:D10;

4. в поле для второй матрицы укажите диапазон G2:G4;

5. нажмите кнопку ОК.

В результате должны получиться значения, представленные на рисунке 64.

Рис. 64. Вычисление корней системы уравнений

Самостоятельно сделайте проверку, для этого умножьте матрицу А на Х. В результате должен получиться столбец В.