Вычислить сумму с учётом указанного знака слагаемого - Мир MS Excel

Регистрация

Напомнить пароль

= Мир MS Excel/Вычислить сумму с учётом указанного знака слагаемого - Мир MS Excel

Старая форма входа

Страница 1 из 2 1 2 »
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычислить сумму с учётом указанного знака слагаемого (Формулы/Formulas)

Вычислить сумму с учётом указанного знака слагаемого

astashov_michail@tutby

Дата: Среда, 10.08.2022, 15:38 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

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

К сообщению приложен файл: 2110611.xlsx (19.1 Kb)

Ответить

Дата: Среда, 10.08.2022, 16:40 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8121

Репутация: 1993 ±

Замечаний: 0% ±

Excel 2010

esΔ=0+(-0,15*(-1))+0,3=0,45
почему 0,3?

ЮMoney 41001841029809

Ответить

astashov_michail@tutby

Дата: Среда, 10.08.2022, 16:54 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

Nic70y, да, прошу прощения - 0,4, забыл изменить :(. Прикрепил изменённый файл

К сообщению приложен файл: 21106113.xlsx (19.1 Kb)

Ответить

Дата: Среда, 10.08.2022, 16:57 | Сообщение № 4

Группа: Друзья

Ранг: Экселист

Сообщений: 8121

Репутация: 1993 ±

Замечаний: 0% ±

Excel 2010

Код

=СУММЕСЛИ()

К сообщению приложен файл: 6336794.xlsx (11.7 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Среда, 10.08.2022, 17:01

Ответить

Дата: Среда, 10.08.2022, 17:08 | Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 96 ±

Замечаний: 0% ±

Excel 2007

Если есть функция "ПОСЛЕД", то можно ещё так
А:

Код

=СУММ(ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(C1:C6;ПОСЛЕД(3;1;1;2)))-СУММ(ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(C1:C6;ПОСЛЕД(3;1;1;2)))

esΔ

Код

=СУММ(-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;2;2)))+СУММ(ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;1;2)))

eiΔ

Код

=СУММ(ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;2;2)))+СУММ(-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;1;2)))

К сообщению приложен файл: 2110611_2.xlsx (12.8 Kb)

Сообщение отредактировал msi2102 - Среда, 10.08.2022, 17:10

Ответить

astashov_michail@tutby

Дата: Среда, 10.08.2022, 17:11 | Сообщение № 6

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

Nic70y, Отлично. Спасибо. А как визуализировать , чтоб после ответы в соседнем столбце было расписано типа -
А=70+(-45)+325=350
esΔ=0+(-0,15*(-1))+0,4=0,55

Ответить

Дата: Среда, 10.08.2022, 17:44 | Сообщение № 7

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 96 ±

Замечаний: 0% ±

Excel 2007

Ну теперь если есть "ПОСЛЕД" и "ОБЪЕДИНИТЬ"

А:

Код

="A="&ОБЪЕДИНИТЬ("";1;ОБЪЕДИНИТЬ("+";1;ЕСЛИ(ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(C1:C6;ПОСЛЕД(3;1;1;2))=0;"";ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(C1:C6;ПОСЛЕД(3;1;1;2))));ЕСЛИ(-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(C1:C6;ПОСЛЕД(3;1;1;2))=0;"";-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(C1:C6;ПОСЛЕД(3;1;1;2))))

esΔ:

Код

="esΔ="&ОБЪЕДИНИТЬ("+";1;ЕСЛИ(-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;2;2))=0;"";-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;2;2)));ЕСЛИ(ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;1;2))=0;"";ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;1;2))))

eiΔ:

Код

="eiΔ="&ОБЪЕДИНИТЬ("";1;ЕСЛИ(ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;2;2))=0;"";ИНДЕКС(B1:B6="+";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;2;2)));ЕСЛИ(-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;1;2))=0;"";-ИНДЕКС(B1:B6="-";ПОСЛЕД(3;1;1;2))*ИНДЕКС(D1:D6;ПОСЛЕД(3;1;1;2))))

К сообщению приложен файл: 6491173.xlsx (13.0 Kb)

Ответить

astashov_michail@tutby

Дата: Среда, 10.08.2022, 18:03 | Сообщение № 8

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

msi2102, я скачал файл ваш, открыл, попробовал изменить в таблице + на - и в формулах #ИМЯ?

Ответить

Дата: Среда, 10.08.2022, 18:15 | Сообщение № 9

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 96 ±

Замечаний: 0% ±

Excel 2007

Цитата astashov_michail@tutby, 10.08.2022 в 18:03, в сообщении № 8 (

)

и в формулах #ИМЯ?

Поэтому и писал: "если есть эти эти функции", если не ошибаюсь ОБЪЕДИНИТЬ появилась в 2019-ом, а ПОСЛЕД в 2021 или 365

Ответить

astashov_michail@tutby

Дата: Среда, 10.08.2022, 18:42 | Сообщение № 10

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

msi2102, у меня 2016

Ответить

astashov_michail@tutby

Дата: Среда, 10.08.2022, 22:00 | Сообщение № 11

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

msi2102, я так понял что в 2016 это реализовать никак, да?

Ответить

Дата: Четверг, 11.08.2022, 08:36 | Сообщение № 12

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 96 ±

Замечаний: 0% ±

Excel 2007

Цитата astashov_michail@tutby, 10.08.2022 в 22:00, в сообщении № 11 (

)

в 2016 это реализовать никак

Не могу Вам точно сказать. После того как установили 2019 LTSC половина формул из башки повылетало, как говорится забыл даже то, чего не знал.
Скорее всего можно, но это нужно ждать формулистов, как вариант макрос, можно написать пользовательскую функцию (UDF)

Ответить

Дата: Четверг, 11.08.2022, 09:03 | Сообщение № 13

Группа: Друзья

Ранг: Экселист

Сообщений: 8121

Репутация: 1993 ±

Замечаний: 0% ±

Excel 2010

[vba]

Код

Sub u_416()
    Application.ScreenUpdating = False
    For Each a In Range("a7:a9")
        b = InStr("À0esei", Left(a, 2))
        d = ""
        For c = 1 To 5 Step 2
            If b = 1 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    f = ""
                    g = ""
                Else
                    f = "("
                    g = "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & f & Range("c" & c) & g
                a.Offset(0, 1) = "=" & d
                a.Offset(0, 2) = a & "=" & d
            ElseIf b = 3 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    u = Range("d" & c).Value
                Else
                    u = "(" & Range("d" & c + 1).Value & "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & u
                a.Offset(0, 1) = "=" & Replace(d, ",", ".")
                a.Offset(0, 2) = a & "=" & d
            ElseIf b = 5 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    u = Range("d" & c + 1).Value
                Else
                    u = "(" & Range("d" & c).Value & "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & u
                a.Offset(0, 1) = "=" & Replace(d, ",", ".")
                a.Offset(0, 2) = a & "=" & d
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

[/vba]

[vba]

Код

Sub u_416()
    Application.ScreenUpdating = False
    For Each a In Range("a7:a9")
        b = InStr("À0esei", Left(a, 2))
        d = ""
        For c = 1 To 5 Step 2
            If b = 1 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    f = ""
                    g = ""
                Else
                    f = "("
                    g = "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & f & Range("c" & c) & g
                a.Offset(0, 1) = "=" & d
                a.Offset(0, 2) = a & "=" & d
            ElseIf b = 3 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    u = Range("d" & c).Value
                Else
                    u = "(" & Range("d" & c + 1).Value & "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & u
                a.Offset(0, 1) = "=" & Replace(d, ",", ".")
                a.Offset(0, 2) = a & "=" & d
            ElseIf b = 5 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    u = Range("d" & c + 1).Value
                Else
                    u = "(" & Range("d" & c).Value & "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & u
                a.Offset(0, 1) = "=" & Replace(d, ",", ".")
                a.Offset(0, 2) = a & "=" & d
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

[/vba]Nic70y

К сообщению приложен файл: 2110611.xlsm (21.1 Kb)

ЮMoney 41001841029809

Ответить

Сообщение[vba]

Код

Sub u_416()
    Application.ScreenUpdating = False
    For Each a In Range("a7:a9")
        b = InStr("À0esei", Left(a, 2))
        d = ""
        For c = 1 To 5 Step 2
            If b = 1 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    f = ""
                    g = ""
                Else
                    f = "("
                    g = "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & f & Range("c" & c) & g
                a.Offset(0, 1) = "=" & d
                a.Offset(0, 2) = a & "=" & d
            ElseIf b = 3 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    u = Range("d" & c).Value
                Else
                    u = "(" & Range("d" & c + 1).Value & "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & u
                a.Offset(0, 1) = "=" & Replace(d, ",", ".")
                a.Offset(0, 2) = a & "=" & d
            ElseIf b = 5 Then
                e = Range("b" & c).Value
                If e = "+" Then
                    u = Range("d" & c + 1).Value
                Else
                    u = "(" & Range("d" & c).Value & "*-1)"
                End If
                x = "+"
                If d = "" Then x = ""
                d = d & x & u
                a.Offset(0, 1) = "=" & Replace(d, ",", ".")
                a.Offset(0, 2) = a & "=" & d
            End If
        Next
    Next
    Application.ScreenUpdating = True
End Sub

[/vba]

Автор - Nic70y
Дата добавления - 11.08.2022 в 09:03

Дата: Четверг, 11.08.2022, 12:43 | Сообщение № 14

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 96 ±

Замечаний: 0% ±

Excel 2007

Ну тогда UDF

[vba]

Код

Public Function Сумма_А(ByRef znak As Range, znach As Range, Optional v As Byte)
Dim n As Integer, c As Double, i As Double
On Error GoTo ErrHand
arr1 = znak.Value
arr2 = znach.Value
For n = LBound(arr1) To UBound(arr1) Step 2
    If arr1(n, 1) <> "-" Then
        arr1(n, 1) = "+"
        Select Case v
            Case Is = 0, 1: k = 0
            Case Is = 2: k = 1
        End Select
        i = 1
    Else
        Select Case v
            Case Is = 0, 2: k = 0
            Case Is = 1: k = 1
        End Select
        i = -1
    End If
    If arr2(n + k, 1) <> "" And arr2(n + k, 1) <> 0 Then m = m & "+" & arr2(n + k, 1) * i: c = c + arr2(n + k, 1) * i
Next n
Select Case v
    Case Is = 0: s = "A"
    Case Is = 1: s = "es" & ChrW(916)
    Case Is = 2: s = "ei" & ChrW(916)
End Select
Сумма_А = Replace(s & "=" & Mid(m, 2) & "=" & c, "+-", "-")
Exit Function
ErrHand:
Сумма_А = "Ошибка"
End Function

[/vba]

Код

=Сумма_А("Диапазон_знаков";"Диапазон_значений";"0 или ""-сумма А, 1-сумма es, 1-сумма ei")

К сообщению приложен файл: 2110611_3.xlsm (21.6 Kb)

Сообщение отредактировал msi2102 - Четверг, 11.08.2022, 12:44

Ответить

astashov_michail@tutby

Дата: Четверг, 11.08.2022, 21:31 | Сообщение № 15

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

msi2102, огромное СПАСИБО, за проделанную Вами работу, но к моему стыду, по мимо того что у меня эксель 2016, так ещё и макросами не дружу :(. и по этому если изменятся немного условия в переменных я наверное не разберусь в VBA. Буду пробовать как-нибудь по старинки пробовать формулами справится. ещё раз спасибо

Ответить

Дата: Пятница, 12.08.2022, 08:36 | Сообщение № 16

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 96 ±

Замечаний: 0% ±

Excel 2007

Цитата astashov_michail@tutby, 11.08.2022 в 21:31, в сообщении № 15 (

)

так ещё и макросами не дружу :(

Так в чем проблема, подружитесь, там не так сложно как Вам кажется, по крайней мере, для того, чтобы скопировать код макроса в стандартный модуль, нужно нажать три кнопки. Почитайте ТУТ или ТУТ

Цитата astashov_michail@tutby, 11.08.2022 в 21:31, в сообщении № 15 (

)

и по этому если изменятся немного условия в переменных я наверное не разберусь в VBA

Последняя функция из сообщения №14, частично решает эту проблему. например если будет не три строки, а 5 или 6 ей без разницы, она их обработает, главное, чтобы формат таблицы сохранялся

К сообщению приложен файл: 6663287.xlsm (26.0 Kb)

Ответить

Дата: Пятница, 12.08.2022, 08:55 | Сообщение № 17

Группа: Друзья

Ранг: Экселист

Сообщений: 8121

Репутация: 1993 ±

Замечаний: 0% ±

Excel 2010

Цитата astashov_michail@tutby, 11.08.2022 в 21:31, в сообщении № 15 (

)

по старинки

апдэйт
не, че-то не допилил, может потом.
теперь вроде норм.

К сообщению приложен файл: 4999555.xlsx (12.6 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Пятница, 12.08.2022, 11:05

Ответить

Дата: Пятница, 12.08.2022, 11:04 | Сообщение № 18

Группа: Проверенные

Ранг: Обитатель

Сообщений: 281

Репутация: 96 ±

Замечаний: 0% ±

Excel 2007

Цитата astashov_michail@tutby, 11.08.2022 в 21:31, в сообщении № 15 (

)

как-нибудь по старинки пробовать формулами

Если есть функция "=СЦЕП()" (по моему она появилась как раз 2016), с доп столбцом (наверное возможно и без него, но лень думать)
Доп.столбец:
E2:

Код

=ЕСЛИ(B2="+";D2;-D3)

E3:

Код

=ЕСЛИ(B2="+";D3;-D2)

Выделить обе ячейки и протянуть вниз.
A:

Код

="A="&ЕСЛИ(B2="-";"-";"")&ПСТР(ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=0;"+"&(C2:C7*(B2:B7&1));""));"+-";"-");2;999)

esΔ:

Код

="esΔ="&ЕСЛИ(ЛЕВСИМВ(ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=0;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-");1)="+";ПСТР(ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=0;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-");2;999);ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=0;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-"))

eiΔ:

Код

="eiΔ="&ЕСЛИ(ЛЕВСИМВ(ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=1;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-");1)="+";ПСТР(ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=1;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-");2;999);ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=1;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-"))

Если не будет смущать "+" в начале, то можно короче

Код

="esΔ="&ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=0;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-")

Код

="eiΔ="&ПОДСТАВИТЬ(СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=1;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");""));"+-";"-")

К сообщению приложен файл: 1941499.xlsx (12.8 Kb)

Сообщение отредактировал msi2102 - Пятница, 12.08.2022, 12:08

Ответить

astashov_michail@tutby

Дата: Пятница, 12.08.2022, 16:00 | Сообщение № 19

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

msi2102, Всем большое спасибо за помощь

Ответить

astashov_michail@tutby

Дата: Пятница, 12.08.2022, 16:20 | Сообщение № 20

Группа: Пользователи

Ранг: Участник

Сообщений: 70

Репутация: 0 ±

Замечаний: 40% ±

msi2102, СЦЕП(ЕСЛИ(ОСТАТ(СТРОКА(B2:B7);2)=0;ЕСЛИ(E2:E7<>0;"+"&E2:E7;"");"")) пишет #ИМЯ?
и если изменяю значение переменной, то тоже пишет #ИМЯ?

Сообщение отредактировал astashov_michail@tutby - Пятница, 12.08.2022, 16:23

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вычислить сумму с учётом указанного знака слагаемого (Формулы/Formulas)

Страница 1 из 2
1
2
»

© 2010-2023 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!