ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 17.04.2021
Просмотров: 1384
Скачиваний: 2
16
6.
Высчитайте
для
данного
множества
суммарных
выручек
магазинов
,
сколько
значений
попадает
в
интервалы
от
0
до
1000,
от
1001
до
1100,
от
1101
до
1200
и
свыше
1201
млн
.
руб
.
Для
этого
в
диапазон
ячеек
J3:J6
вве
-
дите
формулу
{=
ЧАСТОТА
(
Е
3:
Е
8; I3:I5)}
.
Частоты
можно
также
вычислить
с
помощью
команды
Сер
-
вис
→
Анализ
данных
.
Средство
анализа
данных
является
одной
из
надстро
-
ек
Excel.
Если
в
меню
Сервис
отсутствует
команда
Анализ
данных
,
то
для
ее
установки
необходимо
выполнить
команду
Сер
-
вис
→
Надстройки
→
Пакет
анализа
.
После
выбора
пункта
Гистограмма
откроется
окно
В
поле
Входной
интервал
введите
диапазон
Е
3:
Е
8,
по
которому
строим
диаграмму
.
В
поле
Интервал
карманов
введите
диапазон
I3:I5
со
значениями
верхних
границ
интервалов
.
В
поле
выходной
интервал
укажи
-
те
$
К
$3.
На
рисунке
23
приведен
результат
построения
гистограммы
:
Рисунок
23
17
Расчет
итоговой
выручки
по
объему
реализации
Рисунок
24
В
ячейки
А
3:
С
3
введены
стоимости
трех
различных
товаров
,
а
в
ячейки
В
6:D8 –
объемы
их
реализации
по
месяцам
.
Суммарную
стоимость
реализованных
товаров
по
месяцам
можно
рассчитать
двумя
способами
:
1
способ
.
Выделите
диапазон
ячеек
Е
6:
Е
8
и
введите
формулу
:
{=
МУМНОЖ
(
В
6:D6);
ТРАНСП
($
А
$3:$
С
$3)}
2
способ
.
В
ячейку
F6
введите
формулу
=
СУММПРОИЗВ
(B6:D6;$
А
$3:$
С
$3)
и
протяните
на
ячейки
F7:F7.
Ведомость
по
расчету
просроченных
платежей
Рассмотрим
пример
составления
отчетной
ведомости
фирмы
,
про
-
дающей
компьютеры
,
позволяющей
определить
количество
и
сумму
про
-
сроченных
клиентами
платежей
:
Рисунок
25
1.
В
ячейку
Е
2
введите
формулу
,
определяющую
срок
просрочки
платежа
=
ЕСЛИ
(D2=0;$H$2-C2;" ")
,
которую
протащите
на
диапазон
Е
3:
Е
20.
18
2.
В
ячейки
F8, F9
и
F10
соответственно
введены
формулы
{=
СУММ
((
Е
2:
Е
20>0)*(
Е
2:
Е
20<=29)*(B2:B20))}
{=
СУММ
((
Е
2:
Е
20>=30)*(
Е
2:
Е
20<=39)*(B2:B20))}
{=
СУММ
((
Е
2:
Е
20>=40)*(B2:B20))},
вычисляющие
суммарные
стоимости
просроченных
оплат
сроком
до
29
дней
,
от
30
до
39
дней
и
свыше
40
дней
.
Поясним
третью
формулу
: Excel
в
формуле
массива
возвращает
условие
(
Е
2:
Е
20>=40)
в
виде
массива
,
со
-
стоящего
из
0
и
1,
где
0
стоит
на
месте
ячейки
со
значением
меньше
40
и
1 –
на
месте
ячейки
со
значением
не
меньше
40.
Следовательно
,
данная
формула
вычисляет
сумму
произведений
элементов
массива
(
Е
2:
Е
20>=40)
(
с
единицами
в
случае
просрочки
на
указанный
срок
и
нулями
–
в
против
-
ном
случае
)
и
массивы
В
2:
В
20 (
с
ценами
процессоров
).
Таким
образом
,
третья
формула
возвращает
суммарную
стоимость
заказов
,
просроченных
не
менее
чем
на
40
дней
.
3.
В
ячейки
F2, F3
и
F4
соответственно
введены
формулы
{=
СУММ
((
Е
2:
Е
20>0)*(
Е
2:
Е
20<=29))}
{=
СУММ
((
Е
2:
Е
20>=30)*(
Е
2:
Е
20<40))}
=
СЧЕТЕСЛИ
(
Е
2:
Е
20; “>=40”),
вычисляющие
количество
просроченных
оплат
сроком
до
29
дней
,
от
30
до
39
дней
и
свыше
40
дней
.
Ведомости
по
расчету
затрат
на
производство
Предположим
,
что
фирма
производит
CD-
диски
.
Упаковка
диска
об
-
ходится
фирме
в
1
руб
./
шт
.,
стоимость
материалов
– 4
руб
./
шт
.
Готовые
диски
фирма
продает
по
цене
10
руб
./
шт
.
Технические
возможности
фир
-
мы
позволяют
выпускать
до
5000
дисков
в
день
.
Оплата
труда
рабочих
сдельная
и
зависит
от
количества
выпущенных
дисков
.
За
первую
тысячу
дисков
оплата
труда
рабочих
составляет
0,3
руб
./
шт
.,
за
вторую
тысячу
дисков
– 0,4
руб
./
шт
.,
за
третью
тысячу
дисков
– 0,5
руб
./
шт
.,
за
четвертую
тысячу
дисков
– 0,6
руб
./
шт
.
и
свыше
4000
дисков
– 0,7
руб
./
шт
.
Фирме
поступил
заказ
на
изготовление
4500
С
D-
дисков
.
Необходимо
подсчитать
суммарные
издержки
и
прибыль
от
выполнения
данного
заказа
.
Для
упрощения
чтения
формул
присвоим
с
помощью
команды
Вставка
→
Имя
→
Присвоить
диапазонам
D2:D7, E2:E7, F2:F7
и
ячейке
В
1,
соответственно
имена
:
ДискиШт
,
ОплатаРубШт
,
ОплатаРуб
,
ЗаказШт
.
Зарплата
рабочих
,
в
зависимости
от
объема
выпущенных
дисков
,
на
-
ходится
в
диапазоне
F2:F7
и
вычисляется
по
формуле
:
{=
ЕСЛИ
(
ЗаказШт
-1000>
ДискиШт
;1000*
ОплатаРубШт
;
ЕСЛИ
(
ЗаказШт
>
ДискиШт
;(
ЗаказШт
-
ДискиШт
)*
ОплатаРубШт
;0))}
Фигурные
скобки
в
начале
и
конце
формулы
являются
признаком
массива
и
вводятся
нажатием
Ctrl+Shift+Enter
либо
после
завершения
вво
-
да
формулы
,
либо
в
процессе
ее
редактирования
.
На
рисунках
26
и
27
приведен
расчет
затрат
на
производство
с
чи
-
словыми
данными
и
формулами
:
19
Рисунок
26
Рисунок
27
Тема
3.
Создание
табличной
базы
данных
сотрудников
.
База
данных
как
способ
хранения
и
обработки
различной
информа
-
ции
играют
в
настоящее
время
огромную
роль
.
В
базах
данных
хранятся
сведения
о
клиентах
,
заказах
,
справочники
адресов
и
телефонов
и
т
.
д
.
Для
учета
данных
о
сотрудниках
на
предприятиях
используют
раз
-
нообразные
методы
,
рассмотрим
учет
с
помощью
Excel.
Формирование
списка
.
Аналогом
простой
базы
в
Excel
служит
список
.
Список
–
группа
строк
таблицы
,
содержащая
связанные
данные
,
причем
каждый
столбец
списка
содержит
однотипные
данные
.
Предположим
,
что
перечень
столбцов
списка
,
который
будет
приме
-
няться
при
создании
базы
данных
,
набран
в
текстовом
редакторе
Word.
1.
Откройте
документ
в
MS Word
и
наберите
в
один
столбец
:
1.
Порядковый
номер
;
2.
Табельный
номер
;
3.
Фамилия
;
4.
Имя
;
5.
Отчество
;
6.
Отдел
;
7.
Должность
;
8.
Дата
приема
на
работу
;
9.
Дата
увольнения
;
10.
Пол
;
11.
Улица
;
12.
Дом
;
13.
Квартира
;
14.
Домашний
телефон
;
15.
Дата
рождения
;
16.
Идентификационный
код
;
17.
Количество
детей
;
18.
Льготы
по
ПН
;
20
19.
Совместитель
-
многодетный
;
20.
Непрерывный
стаж
с
;
21.
Справочный
столбец
.
2.
Перенесите
список
в
Excel,
начиная
с
ячейки
А
2.
3.
Обработайте
перенесенные
текстовые
данные
.
Обратите
внимание
,
что
все
заголовки
оформлены
следующим
образом
:
порядковый
номер
;
точка
;
пробел
;
текст
заголовка
;
точка
с
запятой
.
Необ
-
ходимо
очистить
текст
от
лишних
символов
,
для
этого
:
в
ячейку
В
2
введите
формулу
=
ДЛСТР
(
А
2)
для
определения
длины
текста
заголовка
,
протяните
формулу
на
диапазон
В
3:
В
22;
в
ячейку
С
2
введите
формулу
=
ЛЕВСИМВ
(
А
2;
В
2-1)
для
удаления
по
-
следнего
символа
из
заголовка
;
в
ячейку
D2
введите
формулу
=
ПРАВСИМВ
(
С
2;
В
2-4)
для
удаления
начальных
символов
из
заголовка
;
В
результате
таблица
с
формулами
примет
вид
:
Рисунок
28
создайте
в
столбце
D
сложную
формулу
для
обработки
текста
,
для
этого
:
−
активизируйте
ячейку
В
4
и
в
режиме
правки
в
строке
формул
скопируйте
находящуюся
в
этой
ячейке
формулу
без
знака
равенства
;
−
нажмите
Enter
и
поместите
табличный
курсор
в
ячейку
С
4;
−
в
строке
формул
выделите
ссылку
на
адрес
ячейки
В
4
и
вместо
этой
ссылки
вставьте
содержимое
буфера
обмена
и
т
.
д
.
В
результате
полу
-
чится
формула
:
=
ПРАВСИМВ
(
ЛЕВСИМВ
(A2;
ДЛСТР
(A2)-1);
ДЛСТР
(A2)-4
),
проверьте
правильность
созданной
формулы
,
удалив
столбцы
В
и
С
;
4.
Перенесите
заголовки
из
столбца
в
строку
:
выделите
и
скопируйте
в
буфер
обмена
полученный
после
обработки
текст
;
поместите
табличный
курсор
в
ячейку
А
1,
которая
будет
служить
началом
строки
заголовка
списка
;
из
контекстного
меню
выберите
Специальная
вставка
;
отметьте
опции
значения
и
транспонировать
,
Ок
.
5.
Введите
данные
в
базу
данных
.