koobas.hobune.stream

Apr MAY Jun 21 2022 2023 2024 1 capture 21 May 23 - 21 May 23

Close Help

Microsoft Office. Excel.

Обработка данных

• Сортировка
  • Сортировка списков
  • Сортировка строк по возрастанию данных в одном столбце
• Подведение итогов
  • Вычисление общих и промежуточные итогов
  • Подведение промежуточных итогов
• Работа с матрицами
• Логические функции
• Сводная таблица
• Поиск решения
  • Пример использования функции Поиск решения

Сортировка списков

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

По умолчанию списки сортируются в алфавитном порядке.

Сортировка строк по возрастанию данных в одном столбце

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

1. Укажите ячейку в сортируемом списке.
2. Нажмите кнопку По возрастанию

Примечание. В сводной таблице элементы в выделенном поле сортируются по алфавиту в возрастающем порядке (от А до Я). Числа сортируются от меньшего к большему.

Вычисление общих и промежуточные итогов

Для вычисления значений итогов используется итоговая функция, например, СУММ или СРЗНАЧ. Промежуточные итоги могут быть отображены в списке с помощью нескольких типов вычислений одновременно.

Общие итоги подводятся с помощью детальных данных, а не с помощью значений промежуточных итогов. Например, итоговая функция СРЗНАЧ возвращает значение для всех детальных данных списка, но не для промежуточных итоговых значений.

Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении детальных данных.

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

1. Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог.
2. Укажите ячейку в этом списке т.е. название столбика (например, h).
3. Выберите команду Итоги в меню Данные.
4. Выберите столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

5. Выберите функцию, необходимую для подведения итогов, из списка Операция.
6. Выберите столбцы, содержащие значения, по которым необходимо подвести итоги, в списке Добавить итоги по.

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

Сумма

Сумма чисел. Эта операция используется по умолчанию для подведения итогов по числовым полям.

Кол-во значений

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

Несмещенное отклонение

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

Смещенное отклонение

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

Несмещенная дисперсия

Несмещенная оценка дисперсии генеральной совокупности по выборке данных.

Смещенная дисперсия

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

Работа с матрицами

1. Ввести элементы матрицы (матриц).
2. Прежде чем вызвать функцию работы с матрицами, выделите область, в которую будет помещен результат (в соответствии с правилами алгебры).
3. Выбрать с помощью мастера функций соответствующую функцию для того, чтобы произвести необходимые действия над матрицей (матрицами).
4. Нажать клавишу <F2>,а затем сочетание клавиш <Ctrl>+<Shift>+<Inter>.

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

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

 

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

Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Функция ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) возвращает одно значение, если заданное лог_выражение при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ Например, ЕСЛИ(А1=2;СУММА(В2:В10);ЕСЛИ(А1=6;СУММА(Вб:В10);""))

Для связки нескольких выражений, содержащих операторы сравнения, удобно использовать следующие логические функции:

Функция	Назначение
И(выр1;выр2;…)	Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
ИЛИ(выр1;выр2;…)	Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
НЕ(выр1;выр2;…)	Меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.

 

Подробнее:

Необходимо интервал значений разбить на 10 частей, для этого:

• Взять сумму модулей первого и последнего чисел, разделить ее на 10 - получим шаг.
• Посчитать сколько чисел входит в каждый интервал, используя математическую функцию СУММЕСЛИ.
  Пример: надо посчитать, сколько чисел из диапазона,
  -9 -7 -6 -5 -4 -2 -1
  (которые содержатся в ячейках с A1 : G1), каждое из которых встречается соответственно
  1 1 1 1 3 3 1 2
  раз, (они содержатся в ячейках с A2 : G2) будет меньше числа -5,7. Для этого Вставка > Функция > Математические > СУММЕСЛИ > и заполняем диалоговое окно по образцу:

В ответе получаем 3.

 

Сводная таблица

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

Сводная таблица: Ввести название столбика. Упорядочить по возрастанию. На панели инструментов Данные > Сводная таблица > (появляется диалоговое окно)

Ставим галочку, как показано на рисунке и нажимаем Далее. Появляется следующее окно: В строке диапазон указать столбик с которым будем работать. Нажимаем Далее.

Мышью захватываем кнопку с названием нашего столбца и тянем на поле с названием Данные. Делаем на ней двойной щелчок и в разделе Операция выбираем нужную операцию, например, сумму. Проделываем эту последовательность действий нужное число раз и нажимаем Далее. Появляется следующее окно. Так как нашу таблицу необходимо поместить на существующий лист, поэтому в строке диапазона надо указать место, где будет находиться наша сводная таблица. Для этого необходимо сделать щелчок мышью на нужном месте, и нажимаем Готово.

Транспортная задача.

Имеется m пунктов A1, …,A m поставщиков однородного продукта и n пунктов B1, …,Bn потребителей этого продукта.

• ai – объем производства в пункте Ai.
• bj – объем потребления в пункте Bj.
• cij – стоимость перевозки единицы продукции от Ai в Bj (удельные транспортные издержки).
• ai , bj , cij >= 0.

Требуется найти план перевозок, который:

1. Не выводит за пределы производительности поставщиков
2. Полностью удовлетворяет спрос всех потребителей
3. Минимизирует суммарные транспортные затраты.

В примере приведена таблица, в клетках которой поставлены элементы матрицы C = {cij}, справа от таблицы - значения величин ai , внизу - значения величин bj транспортной задачи в матричной постановке.

 Пример:  

16	30	17	10	16		4
30	27	26	9	23		6
13	4	22	3	1		10
3	1	5	4	24		10
7	7	7	7	2

 

 

Решение примера

Подготовка данных нашей задачи для решения функцией Поиск решения.

После подготовки на панели инструментов Сервис > Поиск решения. В диалоговом окне вводим данные. Для нашей задачи получим следующее:

Когда ввели все данные нажимаем Выполнить. Получим следующее окно:

После нажатия ОК получим:

Т.е. получили такой оптимальный план, при котором все ограничения выполнены и целевая функция, при подстановке этого плана, минимальна.

Матрица X наглядно показывает, как выглядит оптимальный план. А начальное значение целевой функции f(x) изменилось и стало равным 191.