Главная Вход в систему Регистрация
Задать вопрос Консультации Пользователи Форум
О системе Помощь
Задать вопрос экспертам
Консультация № 184485
17.11.2011, 16:10
126.57 руб.
17.11.2011, 19:48
0 9 1
Компьютеры Программное обеспечение
Уважаемые эксперты! Пожалуйста, ответьте на вопрос:
Задание по Excel: С помощью функции генерации случайных чисел, построить таблицу, представляющую данные о 100 вкладах в сбербанке. Величина вкладов должна равномерно распределяться в пределах от 100 тыс. руб. до 10 млн. руб. После построения таблицы преобразовать формулы в значения.

На основании этой таблицы построить табл. 2 следующего вида:
Пределы
вкладов Количество Сумма Среднее
значение
---------------------------------------------------------------
Менее 2 млн.
---------------------------------------------------------------
2млн.-4 млн.
---------------------------------------------------------------
4млн.-6 млн.
-------------------------------------------------------------------
6млн.-10 млн.
---------------------------------------------------------------
ИТОГО


Построить гистограмму отражающую количество и среднюю величину вклада в каждом из приведенных интервалов. При этом количество и средняя величина должны строиться на разных осях. Количество должно представляться линией, а средняя величина - столбиком.

Обсуждение

Неизвестный
17.11.2011, 18:36
общий
никогда не нравились формулы.
без заполнения вспомогательных столбцов сложно сделать.
т.е. формула в ячейках
= ОКРУГЛ ( 100000 + СЛЧИС ( ) * 9900000 ; 0 )
работает;
суммирование
= СУММЕСЛИ ( Лист1!A1:A100 ; "<2000000" ; Лист1!A1:A100)
тоже работает корректно,
но если поставить сложное условие типа >=2000000 И <4000000 формула перестает работать. да и подсчет количества глючит.
если бы добавить вспомогательные столбцы, в которых было видно, к какому диапазону принадлежит число, то что-то можно было бы сделать. например так :
само число необходимо для подсчета кол-ва <2000000 >=2000000 <4000000 >=4000000 <6000000 >=6000000
8709961 1 0 0 0 1

тогда по значениям контрольных столбцов можно было бы определить сумму и количество в диапазонах
Неизвестный
17.11.2011, 18:54
общий
что-то наваял. вот ссылка

файлик для консультации 184485

если будут вопросы - завтра отвечу.
давно
Сучкова Татьяна Михайловна
Мастер-Эксперт
680
2811
17.11.2011, 19:45
общий
это ответ
Здравствуйте, Наталия!
В столбце B - формулы для расчета размера вкладов, значения на один из моментов времени скопированы и вставлены с помощью Специальная вставка - Значения в столбец C.
Формулы в таблице, по которой строятся гистограммы, такие:

[table]
[row][col]Пределы вкладов [/col][col]Количество [/col][col] Сумма[/col][col] Среднее[/col][/row]
[row][col]Менее 2 млн. [/col][col]=СЧЁТЕСЛИ($C2:$C101;"<=2000000") [/col][col]=СУММЕСЛИ($C2:$C101;"<=2000000") [/col][col] =J5/I5[/col][/row]
[row][col]2млн.-4 млн. [/col][col] =СЧЁТЕСЛИ($C2:$C101;"<=4000000")-I5 [/col][col]=СУММЕСЛИ($C2:$C101;"<=4000000")-J5 [/col][col]=J6/I6 [/col][/row]
[row][col]4млн.-6 млн. [/col][col]=СЧЁТЕСЛИ($C2:$C101;"<=6000000")-I6-I5 [/col][col]=СУММЕСЛИ($C2:$C101;"<=6000000")-J6-J5 [/col][col]=J7/I7 [/col][/row]
[row][col]6млн.-10 млн. [/col][col]=СЧЁТЕСЛИ($C2:$C101;"<=10000000")-I7-I6-I5 [/col][col] =СУММЕСЛИ($C2:$C101;"<=10000000")-J7-J6-J5 [/col][col] =J8/I8[/col][/row]
[row][col]ИТОГО [/col][col]=СУММ(I5:I8) [/col][col]=СУММ(J5:J8) [/col][col] =J9/I9[/col][/row]
[/table]

Знак $ здесь использовать не обязательно, просто с его помощью в формуле СЧЁТЕСЛИ фиксировался номер столбца, чтобы можно было скопировать эти формулы в соседние ячейки и заменить потом СЧЁТЕСЛИ на СУММЕСЛИ Правкой - Заменой


По обсуждению в мини-форуме дополню.
Обратите внимание на такой момент: в условии написано:
Менее 2 млн.
---------------------------------------------------------------
2млн.-4 млн.


В моем ответе используется знак <=
Это означает, что число 2 млн, если оно встретится, войдет в первый интервал, но не войдет во второй.
Если слова "менее 2 млн" трактовать буквально, то в первой строке в формулах надо поставить знак <, например:
=СЧЁТЕСЛИ($C2:$C101;"<2000000") и =СУММЕСЛИ($C2:$C101;"<2000000")
Тогда число 2 млн, если оно встретится, войдет во второй интервал.
Но для остальных интервалов из условия не ясно, в какие интервалы должны входить граничные значения. Можете их оставить такими, как в примере, или тоже исправить знаки, на Ваше усмотрение.
Прикрепленные файлы:
Неизвестный
18.11.2011, 12:14
общий
18.11.2011, 12:15
Адресаты:
А почему не воспользовались в решении вместо
Код:
=INT(100000+RAND()*9900000)
(у меня английская версия Excel) функцией
Код:
=RANDBETWEEN(100000,10000000)
(в русской версии функция называется СЛУЧМЕЖДУ)?

[offtop]Странно, что в Excel не предусмотрен вариант генерации одних и тех же чисел, т.к. при пересчете ячеек содержимое их меняется, что не всегда требуется. На сайте Microsoft нашел только такой текст
Если требуется использовать функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, можно ввести в строку формул =СЛЧИС(), а затем нажать клавишу F9, чтобы заменить формулу на случайное число.
Но не станешь же в каждую ячейку вводить формулу и потом жать F9, а выделить все ячейки и нажать F9 тоже нельзя - просто произойдет перерасчет. Хотя, может конечно такой функционал и лишний.[/offtop]
Неизвестный
18.11.2011, 17:36
общий
это наверное я всех сбил с толку - предложил использовать такую формулу.
при использовании RANDBETWEEN формула будет проще. согласен.

жаль, что этих функций достаточно много. пока специально каждую не просмотришь, что она делает, будешь использовать самые простые, с чем знаком по VBA. а т.к. я пишу на VB6, то дополнительно копаться лень. я все это делаю программно - в том числе заполнение данными и создание диаграмм. хотя чаще всего я сначала делаю все необходимые действия в Excel, записываю их в макрос и смотрю исходный код макроса. потом вставляю этот код с изменениями в свои программы.


кстати, заметил еще. формулу
=СЧЁТЕСЛИ($C2:$C101;"<6000000")-I6-I5
в случае перекрывающихся условий типа 2млн.-5 млн. ;4млн.-7 млн. и т.д., использовать нельзя. только расписывать оба условия
=СЧЁТЕСЛИ($C2:$C101;"<6000000")-СЧЁТЕСЛИ($C2:$C101;">=4000000")
давно
Сучкова Татьяна Михайловна
Мастер-Эксперт
680
2811
18.11.2011, 19:01
общий
18.11.2011, 19:02
Да нет, не сбил. Просто я давно использую именно эту конструкцию.
давно
Сучкова Татьяна Михайловна
Мастер-Эксперт
680
2811
18.11.2011, 19:01
общий

Вы можете дополнить мой ответ - и для спрашивающего это будет полезно, и для рассылки это будет очень хорошо, да и Вам польза.
Неизвестный
19.11.2011, 10:03
общий
19.11.2011, 10:03
Адресаты:
Функция, которая работает в обоих случаях, наверняка одна и та же, а вариант СЛУЧМЕЖДУ добавлен скорее всего для тех пользователей, кто не знаком с программированием совсем и потому не знает о конструкции X+RND*(Y-X)
Так что это не меняет существенно ничего. Кому тема будет интересна - прочитает все обсуждение и узнает в том числе и о возможных вариантах.
давно
Сучкова Татьяна Михайловна
Мастер-Эксперт
680
2811
19.11.2011, 16:39
общий
Рассылку получают не только зарегистрированные участники портала.Подписчикам с дублирующих площадок нет доступа к мини-форуму.
Форма ответа