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

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

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

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

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

[Chet]

Цитата: Сообщение от Sargrivus Требуется перемножить векторы C:D на E:F , например.

при скалярном умножении двух векторов мы получаем число, равное сумме произведений соответствующих координат исходных векторов... грубо говоря, имеем ячейку =C3*E3+D3*F3

А в чём дело-то? Или я не понял сути задачи, или совсем подзабыл математику...

[Sargrivus]

Цитата: Сообщение от Chet-V при скалярном умножении двух векторов мы получаем число, равное сумме произведений соответствующих координат исходных векторов... грубо говоря, имеем ячейку =C3*E3+D3*F3 А в чём дело-то? Или я не понял сути задачи, или совсем подзабыл математику...

Вот абсолютно неверное утверждение. Это справедливо тогда и только тогда, когда 1) длина функций совпадает + 2) полностью совпадает значение аргументов. Иначе - это непойми что. Абракадабра какая то. И нужно не скалярное произведение, нужно произведение двух функций. f(x) и g(x) то есть функция h(x) = f(x)*g(x).

Вот яркий пример:


Первая колонка аргументов, вторая - функций. То есть, (координата, значение). Дано две функции.

Нужно получить результирующую функцию как произведение двух этих. То есть аргументы в диапазоне [245 , 246], а значение функции, какое получается - такое получается - это перемножение соответствующих функций.

Теперь, надеюсь, стало более понятно? - это не прямое произведение одного на другое. Здесь требуется расширение диапазона значений функции. Другими словами - Делание проекции значения одной из функций на аргументы другой.

Добавлено через 2 часа 6 минут

Цитата: Сообщение от Chet-V уж простите великодушно ... а если графическим методом, а не числовым?.. Построить точечные диаграммы, найти подходящие тренды, получить функции и высчитывать произведения при известных зависимостях?

Во-первых, это возможно вновь только тога, когда имеется относительно простая функция. А вот взять хотя бы элементарную f(x)=sin(x)*e(-x^2), то трендовое приближение как можно построить вообще?

Дальше разговор о точности таких операций. - и это самая больная тема. Если точек много, то линейная интерполяция на внутренний диапазон более, чем удовлетворительна, а подмена истинных значений приближёнными - просто недопустима.


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

[andrey_k]

Sargrivus,
Ваша задача для начала требует математического решения, а его у Вас, похоже, нет.
Excel - инструмент, хоть и универсальный, с большим аппаратом математических функций, но Вашей задачи "одной кнопкой" в нём не решить. По частям - расширение диапазона данных с воссозданием значений функций в этих точках по заданному алгоритму, а затем перемножение векторов - реализовать можно.
Определитесь, как Вам нужно восстанавливать значения функций, и дальше всё вполне механически решается (Excel 2007/2010 поддерживает миллион строк, так что на Ваш диапазон из 10-30 тыс. значений хватит вполне - достаточно будет даже Excel 97). Поскольку Вы не определили для себя метод приближения, для начала просто посмотрите методы, заложенные в Excel (статистические и математические функции), а потом, если будут затруднения с реализацией - обращайтесь

[Sargrivus]

Цитата: Сообщение от andrey_k Sargrivus, Ваша задача для начала требует математического решения, а его у Вас, похоже, нет. Excel - инструмент, хоть и универсальный, с большим аппаратом математических функций, но Вашей задачи "одной кнопкой" в нём не решить. По частям - расширение диапазона данных с воссозданием значений функций в этих точках по заданному алгоритму, а затем перемножение векторов - реализовать можно. Определитесь, как Вам нужно восстанавливать значения функций, и дальше всё вполне механически решается (Excel 2007/2010 поддерживает миллион строк, так что на Ваш диапазон из 10-30 тыс. значений хватит вполне - достаточно будет даже Excel 97). Поскольку Вы не определили для себя метод приближения, для начала просто посмотрите методы, заложенные в Excel (статистические и математические функции), а потом, если будут затруднения с реализацией - обращайтесь

Не подумайте, что я человек злобный, но

Цитата: Сообщение от Sargrivus То есть, насколько я понимаю: расширить диапазон задаваемых параметров и аргументов одного массива до значений другого (для совпадения аргументов) и после этого произвести перемножение значений функций. Реально это должно привести к увеличению размерности результирующей таблицы до величины [max(diam(C:D) ; diam(E:F)) , diam(C:D) + diam(E:F)].

Иными словами: сделать проекцию одной функции на аргументы другой и после этого произвести перемножение.

Или вы хотите чтобы я написал алгоритм? - так он стандартный для таких операций. Если говорить про математическое исполнение, то в С++ реализовано без проблем, но возможно ли сделать стандартными операциями Excel - для меня всё-таки загадка... пока...


И ещё

Цитата: Сообщение от andrey_k Sargrivus, Ваша задача для начала требует математического решения, а его у Вас, похоже, нет.

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


Алгоритм:

Производится сверение аргументов таблиц. При несовпадении аргументов – производится подсчёт длины каждого столбца и происходит расширение области определения до наибольшего диапазона.

для не крайних точек:



Оставшиеся крайние точки выбрасываем.



Данная процедура выполняется для обоих массивов (как для массива, отвечающего калибровке детектора, так и для массива, отвечающего калибровке фильтра), иначе могут возникнуть лишние точки в одном из массивов, не имеющие соответствия в другом.


Далее производится перемножение соответствующих элементов с возвратом функции в расширенном диапазоне значений аргументов.

[ДЫСЫпы]

Sargrivus, посмотрите скрин, я правильно понял задачу?
Если да, то полное ли решение, для этого небольшого примера?

[andrey_k]

Цитата: Сообщение от Sargrivus Не подумайте, что я человек злобный, но

злобничать не надо - правильно ставьте вопрос

Цитата: Сообщение от Sargrivus Иными словами: сделать проекцию одной функции на аргументы другой и после этого произвести перемножение. Или вы хотите чтобы я написал алгоритм? - так он стандартный для таких операций. Если говорить про математическое исполнение, то в С++ реализовано без проблем, но возможно ли сделать стандартными операциями Excel - для меня всё-таки загадка... пока...

Excel - не С++ с библиотекой математических функций. Рассмотрите встроенные функции. Если их мало - запрограммируйте свои (есть встроенный Visual Basic; вполне может быть, что трудоемкой конверсии из С++ не понадобится).

Цитата: Сообщение от Sargrivus Чтобы такие вещи писать сначала читайте сообщения полностью. Математическое решение - это представление процесса, а не написание алгоритма, это желательно усвоить раз и на всегда.

Опять же - с первого слова и начинается потрясывание регалиями
Ваш "алгоритм" на финальный похож как решение проблем в известном анекдоте - "станьте ежиками? А как - это не моя задача!" Детали где-то потерялись...

Цитата: Сообщение от Sargrivus Алгоритм: Производится сверение аргументов таблиц.

каким образом: построчно, с выявлением недостающих элементов, по количеству и т.п.?

Цитата: Сообщение от Sargrivus При несовпадении аргументов – производится подсчёт длины каждого столбца и происходит расширение области определения до наибольшего диапазона.

Итоговый диапазон получается пересечением заданных, сложением или выбирается диапазон с наибольшим числом аргументов?

Цитата: Сообщение от Sargrivus для не крайних точек: Открыть картинку

метод наибольших квадратов подходит? В предыдущем своем сообщении Вы вроде как не определись, что он Вам подходит. Если ДА, то применяем функцию ПРЕДСКАЗ() - она как раз это реализует

Цитата: Сообщение от Sargrivus Данная процедура выполняется для обоих массивов (как для массива, отвечающего калибровке детектора, так и для массива, отвечающего калибровке фильтра), иначе могут возникнуть лишние точки в одном из массивов, не имеющие соответствия в другом.

- это уже элементарно - по одинаковому сценарию обрабатываем два массива

Цитата: Сообщение от Sargrivus Далее производится перемножение соответствующих элементов с возвратом функции в расширенном диапазоне значений аргументов.

СУММПРОИЗВ()

[Яриксон]

Здравствуйте!
У меня есть перечень майлов в столбик, пр.:
a@m1
b@m2
c@m3

А мне нужно сделать чтоб он был в строчку и через запятую пр.:
a@m1, b@m2, c@m3

Подскажите пожалуйста как это сделать пошагово.

[andrey_k]

Яриксон,
если кол-во адресов немного или постоянно, то можно объединением:
например,
a@m1 находится в ячейке A3
b@m2 находится в ячейке A4
c@m3 находится в ячейке A5

для получения в ячейке B1 нужного списка вводим:
=A3&", "&A4&", "&A5

Если список большой и операция нужна постоянно, то можно или сделать макрос (обработка списка построчно в цикле и формирование строки), или в ворде: заменить в тексте символы "конец абзаца" на "запятую" и "пробел".

[viking291257]

Здравствуйте! Помогите, пожалуйста, в решении проблемы по работе с датами в Excel( просмотрел почти весь форум, но ответа не нашёл).
Столбец А - содержит даты поступления пациентов ( формат ячейки - дата )
Столбец В - содержит даты выписки пациентов ( формат ячейки - дата )
Столбец С - количество дней между датами поступления и выпиской ( формат ячейки - число )
При использовании формулы "дней360", в строке с незаполненной датой выписки, появляется пятизначное отрицательное число!
Как "научить" Excel не вычислять дни при незаполненных ячейках в столбце В?
Как выбрать из этой таблицы и суммировать дни выписанных напр. в январе, в феврале и т.д.?
Заранее Всем благодарен!

[Chet]

Цитата: Сообщение от viking291257 Как "научить" Excel не вычислять дни при незаполненных ячейках в столбце В?

а ЕСЛИ не работает? =ЕСЛИ (В2="";;В2-А2 "/" или как вы там разницу в днях находите...)

Цитата: Сообщение от viking291257 Как выбрать из этой таблицы и суммировать дни выписанных напр. в январе, в феврале и т.д.?

я б использовал Итоги, например... с подсчетом Количества строк (кажется там есть такое)

[viking291257]

Спасибо, что откликнулись. Вставил Вашу формулу - не вставляется. Сообщение об ошибке: предупреждение о неправильном использовании " = " и " - ".
С итогами, спасибо за подсказку, сейчас попробую, хотя если не могу подсчитать дни между датами игнорируя незаполненные ячейки, какие могут быть итоги.

[Chet]

Цитата: Сообщение от viking291257 Вставил Вашу формулу

я ее на кирилическими буквами писал Вы б воспользовались Мастером

[viking291257]

Я написал её латиницей ( названия столбцов).

[Chet]

Цитата: Сообщение от viking291257 Вставил Вашу формулу - не вставляется. Сообщение об ошибке: предупреждение о неправильном использовании " = " и " - ".

Можете показать? ;)

[regist]

Цитата: Сообщение от viking291257 Спасибо, что откликнулись. Вставил Вашу формулу - не вставляется. Сообщение об ошибке: предупреждение о неправильном использовании " = " и " - ".

или можно попробовать сделать через ЕПУСТО