Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

= Мир MS Excel/посчитать сумму чисел и знаков в одной ячейке с условием - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » посчитать сумму чисел и знаков в одной ячейке с условием
посчитать сумму чисел и знаков в одной ячейке с условием
Николай Дата: Вторник, 07.05.2013, 12:17 | Сообщение № 1
Группа: Гости
Добрый день!

помогите составить формулу для подсчета следующих значений в нескольких ячейках

пример: дано 4 ячейки с следующим содержанием:

1??????4???????


требуется посчитать количество вопросов и сумму цифр.

что пробовал:
=ДЛСТР
[vba]
Код
=СУММПРОИЗВ(ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;{"1";"?"};"")))
[/vba]

и то и то работает криво. ДЛСТР считает количество символов, с вопросами все ОК, а цифры добавляют путаницу, он не разделяет 1 от 9.. все пишет как +1

СУММПРОИЗВ тоже не сработало, он ищет по указанным значениям, но опять же если числа больше 1 он их не сумирует
 
Ответить
СообщениеДобрый день!

помогите составить формулу для подсчета следующих значений в нескольких ячейках

пример: дано 4 ячейки с следующим содержанием:

1??????4???????


требуется посчитать количество вопросов и сумму цифр.

что пробовал:
=ДЛСТР
[vba]
Код
=СУММПРОИЗВ(ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;{"1";"?"};"")))
[/vba]

и то и то работает криво. ДЛСТР считает количество символов, с вопросами все ОК, а цифры добавляют путаницу, он не разделяет 1 от 9.. все пишет как +1

СУММПРОИЗВ тоже не сработало, он ищет по указанным значениям, но опять же если числа больше 1 он их не сумирует
Автор - Николай
Дата добавления - 07.05.2013 в 12:17
DV Дата: Вторник, 07.05.2013, 12:23 | Сообщение № 2
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Так:
Код
=ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";))+ПОДСТАВИТЬ(M13;"?";)

несколько:
Код
=СУММПРОИЗВ(ДЛСТР(M11:M13)-ДЛСТР(ПОДСТАВИТЬ(M11:M13;"?";))+ПОДСТАВИТЬ(M11:M13;"?";))

если нет, давайте пример


Сообщение отредактировал DV - Вторник, 07.05.2013, 12:26
 
Ответить
СообщениеТак:
Код
=ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";))+ПОДСТАВИТЬ(M13;"?";)

несколько:
Код
=СУММПРОИЗВ(ДЛСТР(M11:M13)-ДЛСТР(ПОДСТАВИТЬ(M11:M13;"?";))+ПОДСТАВИТЬ(M11:M13;"?";))

если нет, давайте пример
Автор - DV
Дата добавления - 07.05.2013 в 12:23
Serge_007 Дата: Вторник, 07.05.2013, 12:35 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Если могут быть варианты типа 4???7???, то можно так (формула массива):
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M13;СТРОКА($1:$9);1));--ПСТР(M13;СТРОКА($1:$9);1));ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";)))
К сообщению приложен файл: Nikolay.xls (24.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЕсли могут быть варианты типа 4???7???, то можно так (формула массива):
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M13;СТРОКА($1:$9);1));--ПСТР(M13;СТРОКА($1:$9);1));ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";)))
Автор - Serge_007
Дата добавления - 07.05.2013 в 12:35
Николай Дата: Вторник, 07.05.2013, 13:22 | Сообщение № 4
Группа: Гости
Цитата (Serge_007)
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M13;СТРОКА($1:$9);1));--ПСТР(M13;СТРОКА($1:$9);1));ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";)))


нет, вперемешку не будут, число максимум двухзначное обозначающее фактическое количество, и количество вопросов, обозначающих резерв по позиции в списке

попробовал Ваш массив, в приведенном примере (во вложенном файле) 7 1? ??? 4??????3 сумма чисел=15 сумма вопросов 10, не посчиталось, может я что то не правильно применил?

PS в массиве есть фрагмент - "ПОДСТАВИТЬ" что это и как работает я не понял, может из за этого не работает?

Цитата (DV)
Код
=ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";))+ПОДСТАВИТЬ(M13;"?";)


заработало! показало четко сумму чисел + количество знаков.

Только я не понял, а как то можно выделить отдельно сумму чисел и отдельно количество знаков?
 
Ответить
Сообщение
Цитата (Serge_007)
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M13;СТРОКА($1:$9);1));--ПСТР(M13;СТРОКА($1:$9);1));ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";)))


нет, вперемешку не будут, число максимум двухзначное обозначающее фактическое количество, и количество вопросов, обозначающих резерв по позиции в списке

попробовал Ваш массив, в приведенном примере (во вложенном файле) 7 1? ??? 4??????3 сумма чисел=15 сумма вопросов 10, не посчиталось, может я что то не правильно применил?

PS в массиве есть фрагмент - "ПОДСТАВИТЬ" что это и как работает я не понял, может из за этого не работает?

Цитата (DV)
Код
=ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";))+ПОДСТАВИТЬ(M13;"?";)


заработало! показало четко сумму чисел + количество знаков.

Только я не понял, а как то можно выделить отдельно сумму чисел и отдельно количество знаков?
Автор - Николай
Дата добавления - 07.05.2013 в 13:22
Serge_007 Дата: Вторник, 07.05.2013, 13:34 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Николай)
может я что то не правильно применил?
Как применяли? Давайте файл с ошибкой


Цитата (Николай)
число максимум двухзначное
Цитата (Николай)
заработало
При наличии двузначных чисел будет неверный результат

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Николай)
может я что то не правильно применил?
Как применяли? Давайте файл с ошибкой


Цитата (Николай)
число максимум двухзначное
Цитата (Николай)
заработало
При наличии двузначных чисел будет неверный результат
Автор - Serge_007
Дата добавления - 07.05.2013 в 13:34
Николай Дата: Вторник, 07.05.2013, 13:53 | Сообщение № 6
Группа: Гости
в итоге сделал что то такое
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M7:Q7;СТРОКА($1:$99);1));--ПСТР(M7:Q7;СТРОКА($1:$99);1)))-((СУММПРОИЗВ(ДЛСТР(M7:Q7)-ДЛСТР(ПОДСТАВИТЬ(M7:Q7;{"?"};"")))))
считает и числа и знаки:))
 
Ответить
Сообщениев итоге сделал что то такое
Код
=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M7:Q7;СТРОКА($1:$99);1));--ПСТР(M7:Q7;СТРОКА($1:$99);1)))-((СУММПРОИЗВ(ДЛСТР(M7:Q7)-ДЛСТР(ПОДСТАВИТЬ(M7:Q7;{"?"};"")))))
считает и числа и знаки:))
Автор - Николай
Дата добавления - 07.05.2013 в 13:53
Serge_007 Дата: Вторник, 07.05.2013, 13:59 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Эта формула всегда будет возвращать неправильный результат, за исключением случая, когда в исходном диапазоне есть только цифры

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЭта формула всегда будет возвращать неправильный результат, за исключением случая, когда в исходном диапазоне есть только цифры
Автор - Serge_007
Дата добавления - 07.05.2013 в 13:59
Николай Дата: Вторник, 07.05.2013, 14:06 | Сообщение № 8
Группа: Гости
а как то исправить можно?
 
Ответить
Сообщениеа как то исправить можно?
Автор - Николай
Дата добавления - 07.05.2013 в 14:06
DV Дата: Вторник, 07.05.2013, 14:10 | Сообщение № 9
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Цитата (Николай)
а как то можно выделить отдельно сумму чисел и отдельно количество знаков?

Можно:
знаки:
Код
=ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";))

числа:
Код
=--ПОДСТАВИТЬ(M13;"?";)

Для массива формулы вставляем в СУММПРОИЗВ
 
Ответить
Сообщение
Цитата (Николай)
а как то можно выделить отдельно сумму чисел и отдельно количество знаков?

Можно:
знаки:
Код
=ДЛСТР(M13)-ДЛСТР(ПОДСТАВИТЬ(M13;"?";))

числа:
Код
=--ПОДСТАВИТЬ(M13;"?";)

Для массива формулы вставляем в СУММПРОИЗВ
Автор - DV
Дата добавления - 07.05.2013 в 14:10
Николай Дата: Вторник, 07.05.2013, 15:44 | Сообщение № 10
Группа: Гости
Цитата (DV)
Для массива формулы вставляем в СУММПРОИЗВ


Заметил еще 1 нюанс, формула слетает если в интервале попадается пустая ячейка. С этим можно что то сделать или единственное решение нули ставить?
 
Ответить
Сообщение
Цитата (DV)
Для массива формулы вставляем в СУММПРОИЗВ


Заметил еще 1 нюанс, формула слетает если в интервале попадается пустая ячейка. С этим можно что то сделать или единственное решение нули ставить?
Автор - Николай
Дата добавления - 07.05.2013 в 15:44
Serge_007 Дата: Вторник, 07.05.2013, 15:47 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Николай)
С этим можно что то сделать

В СУММПРОИЗВ():
ЕСЛИ(диапазон<>"";формула)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Николай)
С этим можно что то сделать

В СУММПРОИЗВ():
ЕСЛИ(диапазон<>"";формула)
Автор - Serge_007
Дата добавления - 07.05.2013 в 15:47
Гость Дата: Вторник, 07.05.2013, 16:10 | Сообщение № 12
Группа: Гости
Цитата (DV)

Для массива формулы вставляем в СУММПРОИЗВ


И еще нюанс - если в формуле по числам стоят только "?" в ячейке, тоже ошибку выдает((
 
Ответить
Сообщение
Цитата (DV)

Для массива формулы вставляем в СУММПРОИЗВ


И еще нюанс - если в формуле по числам стоят только "?" в ячейке, тоже ошибку выдает((
Автор - Гость
Дата добавления - 07.05.2013 в 16:10
Serge_007 Дата: Вторник, 07.05.2013, 16:17 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Используйте мою формулу, в ней всё учтено сразу

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеИспользуйте мою формулу, в ней всё учтено сразу
Автор - Serge_007
Дата добавления - 07.05.2013 в 16:17
Николай Дата: Среда, 08.05.2013, 10:26 | Сообщение № 14
Группа: Гости
[quote=Serge_007]Используйте мою формулу, в ней всё учтено сразу[/quote]

пробовал формулу, но с ней тоже не все так просто.

применил ее к столбцу из 7 ячеек в котором и пустые клетки и двузначные значения -пример

1?
(пустая ячейка)
???
(пустая ячейка)
20
(пустая ячейка)
5

=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M7:M13;СТРОКА($1:$99);1));--ПСТР(M7:M13;СТРОКА($1:$99);1));ДЛСТР(M7:M13)-ДЛСТР(ПОДСТАВИТЬ(M7:M13;"?";)))

формула посчитала =5 а должно 2+0+3+0+20+0+5=30

формулу применял как массив ктр+шфт+энтр, все ячейки общего формата без пробелов
 
Ответить
Сообщение[quote=Serge_007]Используйте мою формулу, в ней всё учтено сразу[/quote]

пробовал формулу, но с ней тоже не все так просто.

применил ее к столбцу из 7 ячеек в котором и пустые клетки и двузначные значения -пример

1?
(пустая ячейка)
???
(пустая ячейка)
20
(пустая ячейка)
5

=СУММ(ЕСЛИ(ЕЧИСЛО(-ПСТР(M7:M13;СТРОКА($1:$99);1));--ПСТР(M7:M13;СТРОКА($1:$99);1));ДЛСТР(M7:M13)-ДЛСТР(ПОДСТАВИТЬ(M7:M13;"?";)))

формула посчитала =5 а должно 2+0+3+0+20+0+5=30

формулу применял как массив ктр+шфт+энтр, все ячейки общего формата без пробелов
Автор - Николай
Дата добавления - 08.05.2013 в 10:26
DV Дата: Среда, 08.05.2013, 10:35 | Сообщение № 15
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
Код
=СУММПРОИЗВ(ДЛСТР(A1:A8)-ДЛСТР(ПОДСТАВИТЬ(A1:A8;"?";))+ПОДСТАВИТЬ(0&A1:A8;"?";))
 
Ответить
Сообщение
Код
=СУММПРОИЗВ(ДЛСТР(A1:A8)-ДЛСТР(ПОДСТАВИТЬ(A1:A8;"?";))+ПОДСТАВИТЬ(0&A1:A8;"?";))
Автор - DV
Дата добавления - 08.05.2013 в 10:35
Николай Дата: Среда, 08.05.2013, 11:40 | Сообщение № 16
Группа: Гости
[quote=DV]DV[/quote]

Огромное пасибо! вопрос решен

итог:

=СУММПРОИЗВ(ДЛСТР(M4:M31)-ДЛСТР(ПОДСТАВИТЬ(M4:M31;"?";))+ПОДСТАВИТЬ(0&M4:M31;"?";))
для расчета и знаков и цифр

знаки отдельно среди цифр:
=СУММПРОИЗВ(ДЛСТР(M16:M17)-ДЛСТР(ПОДСТАВИТЬ(M16:M17;"?";)))

Числа отдельно среди знаков:
=СУММПРОИЗВ(--ПОДСТАВИТЬ(0&M16:M17;"?";))
biggrin biggrin
 
Ответить
Сообщение[quote=DV]DV[/quote]

Огромное пасибо! вопрос решен

итог:

=СУММПРОИЗВ(ДЛСТР(M4:M31)-ДЛСТР(ПОДСТАВИТЬ(M4:M31;"?";))+ПОДСТАВИТЬ(0&M4:M31;"?";))
для расчета и знаков и цифр

знаки отдельно среди цифр:
=СУММПРОИЗВ(ДЛСТР(M16:M17)-ДЛСТР(ПОДСТАВИТЬ(M16:M17;"?";)))

Числа отдельно среди знаков:
=СУММПРОИЗВ(--ПОДСТАВИТЬ(0&M16:M17;"?";))
biggrin biggrin
Автор - Николай
Дата добавления - 08.05.2013 в 11:40
Мир MS Excel » Вопросы и решения » Вопросы по Excel » посчитать сумму чисел и знаков в одной ячейке с условием
  • Страница 1 из 1
  • 1
Поиск:

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