MS Excel - решение практических задач

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

------------------------------

Вопросы и ответы, а также советы и рекомендации по настройке MS Excel, ошибки в работе программы, решение задач по импорту/экспорту документов, ссылки и обсуждение надстроек для Excel, обучающие материалы и т.п. размещаем в теме MS Excel - настройка, импорт/экспорт.
Если решение задачи не требует применения формул и макросов (VBA), то это обсуждение будет перенесено в тему по настройкам.

Все пожелания по принципам размещения сообщений по этим темам рассматриваются - пишите в личку Andrey_k.

[ДЫСЫпы]

Цитата: Сообщение от Chetiki Понятно, нет?

Я не понял
А сумма во втором столбе как считается? так =СУММ(B1:B200) или по другому? И что значит выбрать строки. Лучше бы конечно файлик слить куда нибудь!

[Chet]

Цитата: Сообщение от DSp А сумма во втором столбе как считается? так =СУММ(B1:B200) или по другому?

главное, чтоб выбранные строки по второму столбцу дали требуемое значение.
Название: 60_ВВВВ.rar
Размер: 6.77 кб
Ссылка для скачивания файла

Цитата: Сообщение от DSp что значит выбрать строки

выбрать и всё тут шоб я знал их в лицо

Речь о днеьгах, поэтому можно только меньше на 0,01

[ДЫСЫпы]

Цитата: Сообщение от Chetiki выбрать и всё тут шоб я знал их в лицо

теперь ясно
строки я так понял можем брать произвольно

Цитата: Сообщение от Chetiki ближе_ к 4 тыщам

меньше или больше 4х?

Добавлено через 1 час 30 минут
Вообщем единственная мысль которая пришла в голову, это воспользоваться надстройкой поиск решения. Рядом с суммой заводим второй столбец где пишем тока 1, еще в одном перемножаем сумму с однерками, обшая сумма по доп столбу будет таже что и в первом, далее "поиском решения", наложив ограничение на столбец с единицами, запросто разбиваю его 0 и 1(там где единицы и есть нужные нам строки). Но затык в том, на таком диапазоне из 220 строк "Поиск решения" у меня ругается на "Слишком много изменяемых ячеек".
Мож оф2007 проглотит?
Или как вариант эту задачку по комбинаторике писать на VBA.

На пятьдесят строк это выглядит
вот так

На 200 строк, этот метод отрабатывает на ура!

[Elirs]

Цитата: Сообщение от Maxon08 Всем доброго времени суток. Помогите, плиз, в таком вопросе: есть большой список данных в форме таблицы. в первом столбце заполнены не все строки, а только по одной для каждого блока данных.(остальные пустые) то есть допустим - "первый", а в следующих столбцах уже идут данные во всех строках. Задача заключается в том что нужно, чтобы в первом столбце были заполнены все строки! то есть до конца блока данных, которые относятся к блоку "первый" в каждой строчке в первом столбце было написано "первый"! конечно можно нажимать 2 раза на нижний правый угол ячейки, но данных очень много и этот способ не удобен. Помогите решить такую задачу.

Я бы попробовал записать макрос на последоательность действий, назначить горячие клавиши или зацыклить макрос

[ленка_]

ПОМОГИТЕ ПЛИЗЗЗ
мне надо чтобы данные из нескольких разных Книг собирались в одну общую Книгу (назовём ее Книга1).
Например, в Книге1 я в первой строке делаю ссылку на Книгу N в ячейку, где попустим в заголовке таблицы написано декабрь. а вторая строка в Книге 1,соответственно, должна брать остальные данные (ну допустим числовые значения из той самой Книги N, где находится таблица "декабрь").

По сути, это функция ЕСЛИ, но она в данном случае не подходит потому что придётся всё равно каждый раз указывать во всех ячейках на какую Книгу ссылаться, а мне надо чтобы я вносила данные в одну ячейку (на исходную Книгу), а все остальные ячейки ссылались уже на ту же Книгу, что и в первой ячейке, но уже на другие ячейки этой Книги.

надеюсь что понятно объяснила

[Maxon08]

Вопрос вот такой возник...
есть массив данных, который устроен следующим образом - в столбцах B,C и D во всех строках стоят данные.
в столбце А данные есть только в одной строке у блока данных, то есть в первой строке есть, в следующих 9(например) нет, затем в 10 строке есть - в следующих 9 нет и т.д.
Необходимо под каждым блоком данных вставить строку с итогами по этому блоку!
то есть 11 строкой становится строка - "ИТОГО" (в столбцах B,C,D в принципе пусть будет пусто)
далее, в 22 строке "ИТОГО" и т.д.

Очень хотелось бы иметь такой макрос!
Помогите пожалуйста.

[Chet]

Maxon08, а объеденить эти 10 ячеек (с данными и пустые) можно? Тогда можно було б воспользоваться Итогами... из меню Данные.

Добавлено через 2 минуты
ленка_, если честно - не представил себе общую картину.. Возможно, было бы понятнее, если б имел от вас какие-то примеры. Можете залить на обменник?

[Maxon08]

Chetiki, просто очень много данных - список из нескольких тысяч строк, а делятся они все на равные блоки строк по 10-15. Придется много объединять...

[ДЫСЫпы]

Цитата: Сообщение от Maxon08 Chetiki, просто очень много данных

Я тут уже выкладывал макросик про проблеме заполнения столбца "А" в данном случае. На базе его пишем другой. Что вроде

Sub Макрос1()
'
'скопирум таблицу на другой лист
Sheets("Лист1").Select
Cells.Select
Selection.Copy
Sheets("Итоги").Select
Cells.Select
ActiveSheet.Paste
Range("A1").Select

With Worksheets("Итоги").UsedRange
r = .Cells(.Rows.Count, .Columns.Count).Row
'c = .Cells(.Rows.Count, .Columns.Count).Column
'Подсчитали строки и столбцы
End With

'применим уже упоминашийся здесь макрос
For i = 1 To r
If Cells(i, 1) <> 0 Then
znach = Cells(i, 1)
Else
Cells(i, 1) = znach
End If

Next i

'вставляем итоги

Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns("A:A").EntireColumn.AutoFit

End Sub

те. смысл, главное запонить столб "А", по сле этого как говорит Chetiki, любые итоги получаем.
файлик-пример здесь

[Chet]

Maxon08, а если отфильтровать по пустым ячейкам и заполнить их ссылкой =R[-1]C?

[Maxon08]

DSp,
Chetiki,
еще раз огромное спасибо за помощь!
сейчас буду переделывать макрос под свои данные!
да и формула полезная!

[vvo1962]

подскажите пожалуйста, как правильно посчитать обратное распределение Стьюдента?
если например посмотреть по таблице при вероятности 0,95 и степенях свободы должно получиться число 1,812. а вот если в Exel записать формулу =СТЬЮДРАСПОБР(0,95;10) получается число 0,064298146.
в чём ошибка?
оч срочно нужно((
только не надо предлагать другие программы, мне нужно выяснить как правильно считать именно в Excel (препод задал)

[basketz]

Цитата: Сообщение от vvo1962 ..при вероятности 0,95 и степенях свободы должно получиться число 1,812. а вот если в Exel записать формулу =СТЬЮДРАСПОБР(0,95;10) получается число 0,064298146. в чём ошибка?

Может, попробовать записать вероятность как 0,05?

[Maxon08]

Цитата: Сообщение от basketz Может, попробовать записать вероятность как 0,05?

тогда получается 2,22813884242587...............

[basketz]

Почитал "Статистику в Excel" Макаровой.
Итак, "Функция обратного распределения Стьюдента используется в
ситуациях, когда известен уровень надежности (или уровень значимости)
и необходимо рассчитать значение /-критерия.
Например, .. исходя из числа степеней свободы k=9 и заданного
уровня надежности 95 % (уровня значимости а = 0,05) находим
значение коэффициента доверия, равное 2,26 (формула
=СТЬЮДРАСПОБР(0,05;9))".

Значит, при вероятности 0,95 и степенях свободы 10 (в рабочую книгу записываем как СТЬЮДРАСПОБР(0,05;10) получается 2,22.
По таблице из книги ("Исследования с-х техники и обработка опытных данных", Хайлис Г.А.) при числе степеней свободы 10 и вероятности 0,95 коэффициент Стьюдента равен 2,2.
Почему у vvo1962 в этом случае должно получиться 1,812?