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

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

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

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

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

[PD_nightmare]

Добрый день уважаемые специалисты!
Ситуация: имеется база данных с численностью, по месяцам получается , что она равна допустим: 24,5 ; 56,7; 120,4 и т.д. Вопрос в том, что иногда приходиться писать формулу для округление с избытком, а иногда писать формулу округления с недостатком.
Вопрос: Можно ли как то через логические условия вывести формулу, чтобы когда численность получалась по правилам математики для округления с недостатком, автоматически ставилось округленное число, когда численность получалась бы по правилам математики для округления с избытком, тоже ставилось в ячейке это округленное число?
Сколько не думал , так ничего и не надумал Может вы поможете.
Заранее благодарен.

[newsletter]

Цитата: Сообщение от PD_nightmare Добрый день уважаемые специалисты! Ситуация: имеется база данных с численностью, по месяцам получается , что она равна допустим: 24,5 ; 56,7; 120,4 и т.д. Вопрос в том, что иногда приходиться писать формулу для округление с избытком, а иногда писать формулу округления с недостатком. Вопрос: Можно ли как то через логические условия вывести формулу, чтобы когда численность получалась по правилам математики для округления с недостатком, автоматически ставилось округленное число, когда численность получалась бы по правилам математики для округления с избытком, тоже ставилось в ячейке это округленное число? Сколько не думал , так ничего и не надумал Может вы поможете. Заранее благодарен.

не уверен что вас понял..но все же может эти 3 варианта помогут:

[PD_nightmare]

Цитата: Сообщение от newsletter не уверен что вас понял..но все же может эти 3 варианта помогут:

Мне нужна формула, которая бы логически в ячейке определяла, где округлять с избытком, где с недостатком, то есть автоматизировать расчет мне нужно
Допустим , 34.5 получилось в ячейке, она из - за логического условия сама автоматически округляет в нужную сторону , вот...

[ivan712]

Используй функцию ЕСЛИ:
Например:
=ЕСЛИ($A$1=1;ОКРУГЛВВЕРХ(A2;0);ОКРУГЛВНИЗ(A2;0))
В случае, если в ячейке A1 будет 1, будет округление вверх,
иначе вниз.
$ означает, что при копировании в другую ячейку
адрес ячейки A1 в формуле менятся не должен.

[PD_nightmare]

Цитата: Сообщение от ivan712 Используй функцию ЕСЛИ: Например: =ЕСЛИ($A$1=1;ОКРУГЛВВЕРХ(A2;0);ОКРУГЛВНИЗ(A2;0)) В случае, если в ячейке A1 будет 1, будет округление вверх, иначе вниз. $ означает, что при копировании в другую ячейку адрес ячейки A1 в формуле менятся не должен.

Понимаете , мне надо по правилам математики.Если как вы советуете присвоить значение ячейки 1 то будет просто округляться, но без правил математики.Мне надо допустим, что если десятые доли в числе х
составляют больше 4 десятых (1.1, 1.2, 1.3, 1.4 ) то производить округление с недостатком, в противном случае с избытком.Жду...

[ivan712]

Цитата: Сообщение от PD_nightmare Понимаете , мне надо по правилам математики.Если как вы советуете присвоить значение ячейки 1 то будет просто округляться, но без правил математики.Мне надо допустим, что если десятые доли в числе х составляют больше 4 десятых (1.1, 1.2, 1.3, 1.4 ) то производить округление с недостатком, в противном случае с избытком.Жду...

А чем не устраивает стандартная функция ОКРУГЛ ?
Она так и работает.
Например, формула =ОКРУГЛ(A2;0)
при A2 = 1,4 дает 1,
а при A2= 1,5 дает 2

[newsletter]

Цитата: Сообщение от PD_nightmare Мне надо допустим, что если десятые доли в числе х составляют больше 4 десятых (1.1, 1.2, 1.3, 1.4 ) то производить округление с недостатком, в противном случае с избытком.Жду...

так вам че обратка нужна? Вы бы сформулировали поточней

[PD_nightmare]

Вопрос исчерпан

[Snakus]

Здравствуйте!

Мне необходимо сравнить числовые значения 2-х столбцов на наличие совпадений/несовпадений, причем размеры их не совпадают (1 столбец содержит больше ячеек с информацией)

Как сделать это в пределах 1 листа (2 таблицы на 1 листе) и в пределах 2-х листов одной книги (сравнение 1 столбца 1-ого листа с 1-ым столбцом 2-ого листа)?

В Excel`е я не спец и не умею пользоваться макросами, поясните пожалуйста по-подробнее.

[vilukin]

Используй функцию Счетесли(;) - Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
СЧЁТЕСЛИ(диапазон;критерий)
Интервал - это одна или несколько ячеек подряд, включающие числа или имена, массивы или ссылки, содержащие числа. Пустые ячейки и текстовые значения не учитываются.

Критерий — критерий в форме числа, выражения, текста или ссылки на ячейку, который определяет, какие ячейки нужно подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки" или B4.

например =СЧЁТЕСЛИ(Лист2!A:A;Лист1!A1) - сравнивает ячейку A1на Лист1-е со столбцом A:A на Лист2.

[Snakus]

2 vilukin

Спасибо!

Я заодно решил поэкспериментировать с ВПР.

У меня все получилось на примере сходимости 1 к 1 т.е. если значения одинаковые (на 1 листе) стоят друг на против друга
=ЕСЛИ(ЕНД(ВПР(A1;B1:B5;2;0));"Не входит";"Входит")

Когда значения стоят не точно на против друг-друга, то результат отображается некорректно.

К примеру:

1 25 Не входит
2 55 Входит
23 56 Не входит
55 2 Не входит

Если отсортировать по-возрастанию, то

1 2 Не входит
2 25 Не входит
23 55 Не входит
55 56 Не входит

Ввел следующем образом формулу
=ВПР(A1;B:B;1;0)

1 0 #Н/Д
2 2 2
10 3 #Н/Д
23 4 #Н/Д
55 25 55
99 55 #Н/Д
100 56 #Н/Д
57 #Н/Д
58 #Н/Д
59 #Н/Д

появился вопрос:
1) Почему если указать Номер_индекса_столбца "2" (который включен в массив и по нему ведется поиск как требует ВПР), ничего не получится?
2) Если указать Диапазон просмотра "1" - ИСТИНА, то он не ищет "точно", а "0"- ЛОЖЬ (приближенно), то все выходит как надо?

[vilukin]

Цитата: Сообщение от Snakus 2 vilukin Спасибо!

Помог? ЖмиСпасибо Справа от моего сообщения

Цитата: Сообщение от Snakus =ВПР(A1;B:B;1;0) 1) Почему если указать Номер_индекса_столбца "2" (который включен в массив и по нему ведется поиск как требует ВПР), ничего не получится? 2) Если указать Диапазон просмотра "1" - ИСТИНА, то он не ищет "точно", а "0"- ЛОЖЬ (приближенно), то все выходит как надо?

1. Внимательно прочитай справку по ВПР. В массив ты вводишь только 1 столбец B:B.
2. Диапазон просмотра - верно наоборот: "0"- ЛОЖЬ (точно!!!).

Добавлено через 54 минуты

Цитата: Сообщение от kow2008 Мне нужно чтобы текст был синим при одном условии и зеленным при другом

Используй Условное форматирование

Цитата: Сообщение от kow2008 Как решить систему урованений методом наименьших квадратов в Экселе

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные и затем возвращает массив, который описывает полученную прямую.

[werst]

Помогите решить такую задачу:
Имеются данные измерения температуры воздуха за несколько лет. Столбец А - дни (более 1000), столбец В - температура.
Нужно найти и выделить все максимумы, минимумы и ноль. Температура не точно равна нулю, т.е. надо найти значение максимально к нему близкое.
Все это найденное дело нужно поместить в столбец С. То есть этот столбец будет выглядеть пустым с редкими вставками: MAX, MIN, ZERO.
К сожалению, я не умею писать макросы...

[vilukin]

Введи эту формулу
=ЕСЛИ(МАКС(B:B)=B2;B2;ЕСЛИ(МИН(B:B)=B2;B2;ЕСЛИ(ИЛИ (И(B2>=0;B2<0,1);И(B2<=0;B2>-0,1));B2;""))) и протащи во все ячейки

0,1 и -0,1 - диапазон внурти которого - температура считается 0

[werst]

К сожалению эта формула найдет только 1 максимум и минимум, а мне надо все пики и спады этой синусоиды. Представьте себе колебания температуры за 10 лет. Одно лето жаркое, другое нет и тд. И нужно найти все пики и нули. Скажем после весны с нулевой температурой пришло лето-пик и надо найти самую высокую температуру в это лето. И так за 10 лет и зим.