Исследование методов обработки экономической информации в табличном процессоре Excel
Введение
Для представления данных в удобном виде используют таблицы.
Компьютер позволяет представлять их в электронной форме, а это дает возможность
не только отображать, но и обрабатывать данные. Класс программ, используемых
для этой цели, называется электронными таблицами.
Особенность электронных таблиц заключается в возможности
применения формул для описания связи между значениями различных ячеек. Расчет
по заданным формулам выполняется автоматически. Изменение содержимого
какой-либо ячейки приводит к пересчету значений всех ячеек, которые с ней
связаны формульными отношениями и, тем самым, к обновлению всей таблицы в
соответствии с изменившимися данными.
Применение электронных таблиц упрощает работу с данными и
позволяет получать результаты без проведения расчетов вручную или специального
программирования. Наиболее широкое применение электронные таблицы нашли в
экономических и бухгалтерских расчетах, но и в научно-технических задачах
электронные таблицы можно использовать эффективно, например для:
• проведения однотипных расчетов над большими наборами
данных;
• автоматизации итоговых вычислений;
• решения задач путем подбора значений параметров,
табулирования формул;
• обработки результатов экспериментов;
• проведения поиска оптимальных значений параметров;
• подготовки табличных документов;
• построения диаграмм и графиков по имеющимся данным.
Одним из наиболее распространенных средств работы с
документами, имеющими табличную структуру, является программа Microsoft Excel.
1.
Логические функции в табличном процессоре Excel
1.1
Функция ЕСЛИ
Возвращает одно значение, если заданное условие при
вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Функция ЕСЛИ используется при проверке условий для значений и
формул.
Синтаксис
ЕСЛИ (лог_выражение; значение_если_истина;
значение_если_ложь)
Лог_выражение - это любое значение или выражение,
принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 - это логическое
выражение; если значение в ячейке A10 равно 100, то выражение принимает
значение ИСТИНА. В противном случае - ЛОЖЬ. Этот аргумент может быть
использован в любом операторе сравнения.
Значение_если_истина - это значение, которое
возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент -
строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ
отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а
значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово
ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента.
Значение_если_истина может быть формулой.
Значение_если_ложь - это значение, которое
возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент -
строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ
отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а
значение_если_ложь опущено (то есть после значение_если_истина нет точки с
запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно
ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит
точка с запятой с последующей закрывающей скобкой), то возвращается значение 0.
Значение_если_ложь может быть формулой.
Замечания
1
До 7
функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов
значение_если_истина и значение_если_ложь для конструирования более сложных
проверок. См. последний из приведенных ниже примеров.
2
Когда
значения аргументов значение_если_истина и значение_если_ложь вычислены,
функция ЕСЛИ возвращает полученное значение.
3
Если
любой из аргументов функции ЕСЛИ является массивом, все элементы массива
вычисляются при выполнении функции ЕСЛИ.
4
Microsoft
Excel предлагает дополнительные функции, которые можно применять для анализа
данных с использованием условий. Например, для вычисления числа появлений
текстовой строки или числа в диапазоне ячеек используйте функцию СЧЁТЕСЛИ. Для
вычисления суммы значений, попадающих в интервал, заданный текстовой строкой
или числами, используйте функцию СУММЕСЛИ. Вычисление значений с использованием
условия.
Примеры
На листе бюджетного отчета ячейка A10 содержит формулу для
расчета текущего бюджете. Если результат вычислений по формуле в ячейке A10
меньше или равен 100, то приведенная ниже функция отображает «В пределах
бюджета». В противном случае функция отображает «Превышение бюджета».
ЕСЛИ (A10<=100; «В пределах бюджета»; «Превышение
бюджета»)
В следующем примере, если значение ячейки A10 - 100, то
лог_выражение имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В
противном случае лог_выражение имеет значение ЛОЖЬ и возвращается пустой текст
(«»), очищающий ячейку, которая содержит функцию ЕСЛИ.
ЕСЛИ (A10=100; СУММ (B5:B15); "»)
Предположим, что рабочий лист по расходам содержит в ячейках
B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500
соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те
же периоды: 900, 900 и 925.
Можно написать формулу для проверки соответствия бюджету
расходов определенного месяца, генерируя тексты сообщений с помощью следующих
формул:
ЕСЛИ (B2>C2; «Превышение бюджета»; «OK») равняется
«Превышение бюджета»
ЕСЛИ (B3>C3; «Превышение бюджета»; «OK») равняется «OK»
Предположим, что нужно назначить буквенную категорию числам,
на которые ссылаются по имени «СреднийБалл». Категории приведены в следующей
таблице.
|
СреднийБалл
|
Категория
|
|
Больше 89
|
A
|
|
От 80 до 89
|
B
|
|
От 70 до 79
|
C
|
|
От 60 до 69
|
D
|
|
Меньше 60
|
F
|
Тогда можно использовать вложенные функции ЕСЛИ:
ЕСЛИ (СреднийБалл>89; «A»; ЕСЛИ (СреднийБалл>79; «B»;
ЕСЛИ (СреднийБалл>69; «C»; ЕСЛИ (СреднийБалл>59; «D»;
«F»))))
В предыдущем примере второе предложение ЕСЛИ является в то же
время аргументом значение_если_ложь для первого предложения ЕСЛИ. Аналогично,
третье предложение ЕСЛИ является аргументом значение_если_ложь для второго
предложения ЕСЛИ. Например, если первое лог_выражение (Среднее>89) имеет
значение ИСТИНА, то возвращается значение «A». Если первое лог_выражение имеет
значение ЛОЖЬ, то вычисляется второе предложение ЕСЛИ и так далее.
1.2
Функция ИЛИ
Возвращает ИСТИНА, если хотя бы один из аргументов имеет
значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис
ИЛИ (логическое_значение1;
логическое_значение2;…)
Логическое_значение1, логическое_значение2,… - это от 1 до 30
проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
1
Аргументы
должны принимать логические значения (ИСТИНА или ЛОЖЬ) или быть массивами или
ссылками, которые содержат логические значения.
2
Если
аргумент, который является ссылкой или массивом, содержит тексты или пустые
ячейки, то такие значения игнорируются.
3
Если
заданный интервал не содержит логических значений, то функция ИЛИ возвращает
значение ошибки #ЗНАЧ!.
4
Можно
использовать функцию ИЛИ как формулу массива, чтобы проверить, имеются ли значения
в массиве. Для того, чтобы ввести формулу массива, нажмите CTRL+SHIFT в
Microsoft Excel для Windows.
Примеры
ИЛИ(ИСТИНА) равняется ИСТИНА
ИЛИ (1+1=1; 2+2=5) равняется ЛОЖЬ
Если ячейки A1:A3 содержат значения ИСТИНА, ЛОЖЬ и ИСТИНА,
то:
ИЛИ (A1:A3) равняется ИСТИНА
1.3
Функция ИСТИНА
Возвращает логическое значение ИСТИНА.
Синтаксис
ИСТИНА()
Замечания
Можно непосредственно ввести значение ИСТИНА в ячейки и
формулы без использования этой функции. Функция ИСТИНА предназначена для
совместимости с другими системами электронных таблиц.
1.4
Функция ЛОЖЬ
Возвращает логическое значение ЛОЖЬ.
Синтаксис
ЛОЖЬ()
Можно непосредственно ввести слово ЛОЖЬ в рабочий лист или в
формулу, и Microsoft Excel будет интерпретировать его как логическое значение
ЛОЖЬ.
1.5
Функция НЕ
Меняет на противоположное логическое значение своего
аргумента. Функция НЕ используется в тех случаях, когда необходимо быть
уверенным в том, что значение не равно некоторой конкретной величине.
Синтаксис
НЕ (логическое_значение)
Логическое_значение - это значение или выражение, которое при
вычислении дает ИСТИНА или ЛОЖЬ. Если логическое_значение имеет значение ЛОЖЬ,
то функция НЕ возвращает значение ИСТИНА; если логическое_значение имеет
значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ.
Примеры
НЕ(ЛОЖЬ) равняется ИСТИНА
НЕ (1+1=2) равняется ЛОЖЬ
2.
Решение задачи
2.1
Входная и выходная информация
В качестве входной информации выступает информация с
исходными данными, которая в соответствии с заданием на курсовую работу
содержится в таблице 1 (информация о себестоимости брака по статьям расхода) и
таблице 2 (данные о проектах в трех цехах).
Выходной информацией являются данные, которые требуется
определить по заданию (отсортировка таблицы по статьям расхода и номерам цехов:
Данные-Сортировка). К выходной информации относятся также ответы на запросы и
гистограмма (график).
2.2
Схема алгоритма
Статьи затрат
|
Статья расходов
|
Название статьи
|
Затраты на
единицу продукции
|
|
01
|
Материалы
|
257,52
|
|
03
|
Заработная
плата
|
600,94
|
|
04
|
Отчисления на
соцстрах
|
738,34
|
|
02
|
Цеховые расходы
|
118,32
|
|
10
|
Стоимость
реализации брака
|
780,25
|
|
11
|
Удержание за
брак
|
690,98
|
Затраты цехов
|
Номер цеха
|
Статья расходов
|
Колиество
издетий
|
|
1
|
01
|
26
|
|
2
|
03
|
189
|
|
3
|
04
|
84
|
|
4
|
02
|
328
|
|
5
|
10
|
627
|
|
6
|
11
|
122
|
|
7
|
01
|
179
|
|
8
|
03
|
172
|
|
9
|
04
|
650
|
|
10
|
02
|
401
|
|
11
|
10
|
383
|
11
|
70
|
|
13
|
01
|
58
|
|
14
|
03
|
402
|
|
15
|
04
|
560
|
Результаты
|
Номер цеха
|
Статья расходов
|
Название статьи
|
Затраты на
единицу продукции
|
Кол-во изделий
|
Суммарные
затраты
|
Показатель 1
|
|
1
|
01
|
Материалы
|
257,52 р.
|
26
|
6 695,52 р.
|
?
|
|
2
|
03
|
Заработная
плата
|
600,94 р.
|
189
|
113 577,66 р.
|
-
|
|
3
|
04
|
Отчисления на
соцстрах
|
738,34 р.
|
84
|
62 020,56 р.
|
?
|
|
4
|
02
|
Цеховые расходы
|
118,32 р.
|
328
|
38 808,96 р.
|
+
|
|
5
|
10
|
Стоимость
реализации брака
|
780,25 р.
|
627
|
489 216,75 р.
|
-
|
|
6
|
11
|
Удержание за
брак
|
690,98 р.
|
122
|
84 299,56 р.
|
-
|
|
7
|
01
|
Материалы
|
257,52 р.
|
179
|
46 096,08 р.
|
-
|
|
8
|
03
|
600,94 р.
|
172
|
103 361,68 р.
|
-
|
|
9
|
04
|
Отчисления на
соцстрах
|
738,34 р.
|
650
|
479 921,00 р.
|
-
|
|
10
|
02
|
Цеховые расходы
|
118,32 р.
|
401
|
47 446,32 р.
|
+
|
|
11
|
10
|
Стоимость
реализации брака
|
780,25 р.
|
383
|
298 835,75 р.
|
-
|
|
12
|
11
|
Удержание за
брак
|
690,98 р.
|
70
|
48 368,60 р.
|
?
|
|
13
|
01
|
Материалы
|
257,52 р.
|
58
|
14 936,16 р.
|
?
|
|
14
|
03
|
Заработная
плата
|
600,94 р.
|
402
|
241 577,88 р.
|
-
|
|
15
|
04
|
Отчисления на
соцстрах
|
738,34 р.
|
560
|
413 470,40 р.
|
-
|
|
1. Средняя
себестоимость брака по каждому цеху
|
|
|
|
|
Номер цеха
|
Средняя
себестоимость брака
|
|
|
|
|
|
1
|
6 695,52 р.
|
|
|
|
|
|
2
|
113 577,66 р.
|
|
|
|
|
|
3
|
62 020,56 р.
|
|
|
|
|
|
4
|
38 808,96 р.
|
|
|
|
|
|
5
|
489 216,75 р.
|
|
|
|
|
|
6
|
84 299,56 р.
|
|
|
|
|
|
7
|
46 096,08 р.
|
103 361,68 р.
|
|
|
|
|
|
9
|
479 921,00 р.
|
|
|
|
|
|
10
|
47 446,32 р.
|
|
|
|
|
|
11
|
298 835,75 р.
|
|
|
|
|
|
12
|
48 368,60 р.
|
|
|
|
|
|
13
|
14 936,16 р.
|
|
|
|
|
|
14
|
241 577,88 р.
|
|
|
|
|
|
15
|
413 470,40 р.
|
|
|
|
|
|
2. Суммарная
стоимость затрат по группе статей расходов с 01 по 04.
|
|
|
|
|
Способ 1
|
1 567 912,22 р.
|
|
|
|
|
|
|
Способ 2
|
1 567 912,22 р.
|
|
|
|
|
|
|
3. Число статей
расхода которые имеет 1 цех
|
|
|
|
|
Способ 1
|
1
|
|
|
|
|
|
|
Способ 2
|
1
|
|
|
|
|
|
|
4. Число статей
расхода имеющих минимальную сумму затрат
|
|
|
|
|
1
|
|
|
|
|
|
Итоги
|
Номер цеха
|
Статья расходов
|
Название статьи
|
Затраты на
единицу продукции
|
Количество
изделий
|
Суммарные
затраты
|
|
01 Всего
|
|
|
|
67 727,76 р.
|
|
02 Всего
|
|
|
|
86 255,28 р.
|
|
03 Всего
|
|
|
|
458 517,22 р.
|
|
04 Всего
|
|
|
|
955 411,96 р.
|
|
10 Всего
|
|
|
|
788 052,50 р.
|
|
11 Всего
|
|
|
|
132 668,16 р.
|
|
Общий итог
|
|
|
|
2 488 632,88 р.
|
Таблицы в формульном виде
Статьи затрат
|
Статья расходов
|
Название статьи
|
Затраты на
единицу продукции
|
|
1
|
Материалы
|
=ОКРУГЛ
(СЛЧИС()*(790-26)+26; 2)
|
|
3
|
Заработная
плата
|
=ОКРУГЛ
(СЛЧИС()*(790-26)+26; 2)
|
|
4
|
Отчисления на
соцстрах
|
=ОКРУГЛ
(СЛЧИС()*(790-26)+26; 2)
|
|
2
|
Цеховые расходы
|
=ОКРУГЛ
(СЛЧИС()*(790-26)+26; 2)
|
|
10
|
Стоимость
реализации брака
|
=ОКРУГЛ
(СЛЧИС()*(790-26)+26; 2)
|
|
11
|
Удержание за
брак
|
Затраты цехов
|
Номер цеха
|
Статья расходов
|
Количество
изделий
|
|
1
|
1
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
2
|
3
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
3
|
4
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
4
|
2
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
5
|
10
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
6
|
11
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
7
|
1
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
8
|
3
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
9
|
4
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
10
|
2
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
11
|
10
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
12
|
11
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
13
|
1
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
14
|
3
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
|
15
|
4
|
=ОКРУГЛ
(СЛЧИС()*(650-2)+2; 0)
|
Итоги
|
Номер цеха
|
Статья расходов
|
Название статьи
|
Затраты на
единицу продукции
|
Кол-во изделий
|
Суммарные
затраты
|
|
01 Всего
|
|
|
|
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ
(9; F2:F4)
|
|
02 Всего
|
|
|
|
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ
(9; F6:F7)
|
|
03 Всего
|
|
|
|
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ
(9; F9:F11)
|
|
04 Всего
|
|
|
|
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ
(9; F13:F15)
|
|
10 Всего
|
|
|
|
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ
(9; F17:F18)
|
|
11 Всего
|
|
|
|
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ
(9; F20:F21)
|
|
Общий итог
|
|
|
|
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ
(9; F2:F21)
табличный процессор статья затраты
В результате выполнения данной курсовой работы по информатике
были освоены методы работы с табличным процессором Excel, с помощью которого
можно производить сложные расчеты при работе с большими объемами табличных
данных. Данный редактор является универсальным и удобным для работы с данными,
которые хранятся в виде таблиц, в тоже время с огромными возможностями
вычислений и простотой в использовании.
Освоены такие методы обработки данных, как Сортировка,
генерирование случайных чисел, мастер функций, мастер диаграмм, позволяющий
наглядно и красиво отображать интересующую нас экономическую информацию,
относительные и абсолютные ссылки, функции обработки баз данных, табличный вид
функций и другие методы.
Список использованных источников
1. Гончаров, А. Excel 7.0 в примерах [Текст]
/А. Гончаров. - СПб.: Питер, 1996. - 256 с.
. Овчаренко, Е.К. Финансово-экономические расчеты
в Excel [Текст]/ Е.К. Овчаренко.
- М.: Информационно-издательский дом «Филинъ», 1998. - 184 с.
. Безруких, Н.С. Информатика[Текст]: Лабораторный
практикум в пакетах Microsoft Windows, Word и Excel для студентов специальностей 06.08, 06.05,
26.02, 25.06, 17.04 заочной формы обучения/ Н.С. Безруких. - Красноярск:
СибГТУ, 2000. - 45 с.
. Лавренов, С.М. Excel. Сборник примеров и задач
[Текст]/ С.М. Лавреонов. - М: Финансы и статистика, 2006. - 335 с.
. Зелинский, С. Excel 2003 [Текст]/ С. Зелинский.
- Питер, 2006. - 320 с.
Похожие работы на - Исследование методов обработки экономической информации в табличном процессоре Excel
|