Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Как посчитать в табеле часы, когда в ячейках цифры и буквы написаны вместе?
 
#1
29.05.2011 16:45:25
В разных ячейках написаны цифры и буквы вместе. Например: 23д, 15н, 18д и т.д. Есть еще просто ячейки с такими обозначениями как б/с, от, н/б, п/р и т.д.  
Как посчитать сумму этих чисел?  
При простой автосумме выдает ошибку, из-за букв.  
Отделить их тоже никак не возможно.  
Как поступить в этом случае?  
Заранее благодарю!
 
 
 
#2
29.05.2011 16:54:30
В предложенной Дмитрием формуле можно ссылаться на список сокращений:  
=СУММ(ЕСЛИ(ЕОШ(-ПОДСТАВИТЬ($A$1:$A$3;ссылка_на_диапазон_с_сокращениями;));;--ПОДСТАВИТЬ($A$1:$A$3;ссылка_на_диапазон_с_сокращениями;)))
 
 
 
#3
29.05.2011 17:07:00
если текст в ячейках всегда начинается с цифр (когда они там вообще есть), то можно использовать простенькую UDF  
 
Function СУММБЕЗБУКВ(r As Range) As Double  
For Each c In r: СУММБЕЗБУКВ = СУММБЕЗБУКВ + Val(c.Value): Next  
End Function
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#4
29.05.2011 22:35:03
Все что вы дали попробовал, но пока что-то не получается.  
Или я что-то делаю не так?  
Посмотрите пожалуйста прикрепленный файл.  
Может будет другой выход.  
Еще раз огромное спасибо всем!!
 
 
 
#5
29.05.2011 22:42:11
?
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
29.05.2011 23:03:47
Спасибо!!  
Но как вы это сделали?  
У меня все равно выдает ошибку на другой книге.    
Вы использовали UDF?  
К сожаления я не знаю про функции, как можно просто скпировать чтобы работало на других книгах?  
Спасибо еще раз!
 
 
 
#7
29.05.2011 23:07:24
читаем: <EM>http://www.excel-vba.ru/chto-umeet-excel/chto-takoe-funkciya-polzovatelyaudf/</EM>  
 
всё очень интересно, просто и понятно.  
и картинки есть.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
29.05.2011 23:32:09
не хотите возиться с UDF - вот Ваш файл с формулой от The_Prist'a (с учетом доработки от kim'а)  
 
обратите внимание - это формула массива. ввод такой формулы надо заканчивать нажанием не Enter, а Ctrl+Shift+Enter
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#9
29.05.2011 23:34:44
Огромное вам спасибо Икки!  
Все получилось!!!  
Удачи Вам!
 
 
 
#10
29.05.2011 23:39:04
{quote}{login=ikki}{date=29.05.2011 11:32}{thema=}{post}не хотите возиться с UDF - вот Ваш файл с формулой от The_Prist'a (с учетом доработки от kim'а)  
 
обратите внимание - это формула массива. ввод такой формулы надо заканчивать нажанием не Enter, а Ctrl+Shift+Enter{/post}{/quote}  
Я сделал с помощью вашей функции, но это тоже пригодится для опыта.  
А как научится VBA-программированию?  
Хотелось бы поглубже освоить эту область.
 
 
 
#11
29.05.2011 23:44:33
можно начать здесь:  
<EM>http://www.firststeps.ru/vba/excel/vbaexcel1.html</EM>  
можно бумажную книгу купить.  
а вообще - ресурсов оч. много, если вежливо спросить гугл или яндекс - они скажут :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#12
29.05.2011 23:45:52
Можно заглянуть в "Копилку"
 
 
 
#13
29.05.2011 23:57:02
Можно сделать вообще по-другому: буквы "н", "д" добавлять форматом ячейки, при этом в ячейках останутся числа и можно пользоваться простыми формулами:
 
 
 
#14
30.05.2011 00:09:25
{quote}{login=Казанский}{date=29.05.2011 11:57}{thema=Артур}{post}Можно сделать вообще по-другому: буквы "н", "д" добавлять форматом ячейки, при этом в ячейках останутся числа и можно пользоваться простыми формулами:{/post}{/quote}  
Алексей, я, конечно, дико извиняюсь, но можно я встряну, пока Артур не бросился реализовывать такое простое решение? :)  
Имеем: шаблон табеля, с пустыми клеточками. нужно заполнить по дням - кто сколько отработал, был в отпуске, на больничном и т.д.  
если отработал - должно отображаться кол-во часов и буква "н" или "д".  
какая буква в какой день для каждого работника понадобится - мы не знаем.  
менять каждый раз формат ячейки - имхо, убиться можно.  
конечно, есть вариант копировать форматы, но за этим надо тщательно следить, ошибиться оч.легко, да и квалификация оператора должна быть чуть выше, чем просто веести в ячейку цифры и букву.  
а уж если захочется посчитать кол-во ночных и дневных часов по отдельности - так это будет полный ступор.  
 
упрощение в одном месте приводит к усложнению во всех остальных :)))  
имхо.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#15
30.05.2011 10:56:51
{quote}{login=ikki}{date=29.05.2011 11:32}{thema=}{post}не хотите возиться с UDF - вот Ваш файл с формулой от The_Prist'a (с учетом доработки от kim'а)  
 
обратите внимание - это формула массива. ввод такой формулы надо заканчивать нажанием не Enter, а Ctrl+Shift+Enter{/post}{/quote}  
Александр, формула в post_232165.xls не корректно считает количество часов, в случае отсутствия каких либо букв после цифр (если введено только число), конечно если такое в реальности будет.  
Формулами можно еще так посчитать (суммирует максимум двузначные числа, но можно переделать и на большее количество):  
=СУММ(--ПСТР(0&D10:AH10;1;МУМНОЖ({1;1;1};1-ЕОШ(-ПСТР(0&D10:AH10;1;{1:2:3})))))
 
 
 
#16
30.05.2011 11:55:57
{quote}{login=MCH}{date=30.05.2011 10:56}{thema=Re: }{post}  
Александр, формула в post_232165.xls не корректно считает количество часов, в случае отсутствия каких либо букв после цифр (если введено только число), конечно если такое в реальности будет.  
{/post}{/quote}  
 
да, действительно...  
причём считает во столько раз больше, чем надо, сколько ячеек в ссылке второго аргумента функции ПОДСТАВИТЬ().  
т.е. сейчас - в два раза больше, а если поменять $E$20:$E$21 на $E$20:$E$22 - то будет в три раза.  
жаль.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#17
30.05.2011 13:06:10
{quote}{login=ikki}{date=30.05.2011 11:55}{thema=Re: Re: }{post}  
да, действительно...  
причём считает во столько раз больше, чем надо, сколько ячеек в ссылке второго аргумента функции ПОДСТАВИТЬ().  
т.е. сейчас - в два раза больше, а если поменять $E$20:$E$21 на $E$20:$E$22 - то будет в три раза.  
жаль.{/post}{/quote}  
Можно вывернутся, алгоритм тот же, немного изменил формулу:  
=СУММ(ЕСЛИ(ЕОШ(-ПОДСТАВИТЬ(D10:AH10&"!";$E$20:$E$22&"!";));0;--ПОДСТАВИТЬ(D10:AH10&"!";$E$20:$E$22&"!";)))  
при этом значения в списке должны быть уникальными, а последняя/первая ячейка пустой
 
 
 
#18
30.05.2011 13:06:39
разлогинился
 
 
 
#19
30.05.2011 17:14:27
=СУММ(ЕСЛИ(ЕОШ(ПОИСК({"д":"н"};$D10:$AH10));;--ПОДСТАВИТЬ($D10:$AH10;{"д":"н"};));$D10:$AH10)
 
 
 
#20
31.05.2011 10:37:28
=СУММ(ЕСЛИ(ЕЧИСЛО(D10:AH10);D10:AH10;ЕСЛИ(ЕЧИСЛО(-ЛЕВСИМВ(D10:AH10));--ПСТР(D10:AH10;1;ДЛСТР(D10:AH10)-1))))
 
 
 
#21
06.07.2011 11:13:33
вот видимо эта тема мне наиболее подходит, почитал что вы пишиите, поразмвшлял, попробовал, не получилось применить к моей таблице, по сему задам вопрос, можно ли написать такую формулу которая будет считать цифры в ячейке до превых букв,символов. просто окончания после цифр очень разные и зачастую с коментами, а мне как понимаете они ненужны=) и небольшое примечание , меня интересуют только формулы (остольное унас отрублено!)
 
 
 
#22
06.07.2011 11:42:08
А нас интересуют Ваши данные. Без них ни мы, ни Вы не удовлетворим свой интерес.
 
 
 
#23
06.07.2011 11:59:00
пример вот
 
 
 
#24
06.07.2011 12:04:43
Уж или здесь, или только в своей теме. Зачем дублировать?  
Только из уважения к заходящим в тему:  
=--ЛЕВСИМВ(RC[-1];ПОИСК("k";RC[-1]&"k")-1)
 
 
 
#25
06.08.2011 01:24:05
Господа, а вот к примеру если задача состоит в том, что в двух последних столбцах (один "итого дневных часов", а второй "итого ночных часов") табеля необходимо посчитать отденьно буквы "н" и "д". При чем учесть что "н" - это часы с 24:00 до 7:00(т.е. 7 часов), а продолжительность смены составляет 11 часов. Тоесть в одном столбце должны посчитаться все "н", они должны быть равными 7, а в другом должны посчитаться все "д"=11 и "н"=4.  
 
Два чача уже голову ломаю, бороздю форумы и ничего не могу найти...  
То что нужно во вложении.
 
 
 
#26
06.08.2011 01:44:52
Привет, Владимирыч, от Владимира.  
 
=СУММ((D4:AH4="д")*D3:AH3) - формула массива.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#27
06.08.2011 03:12:32
{quote}{login=Владимир}{date=06.08.2011 01:44}{thema=}{post}Привет, Владимирыч, от Владимира.  
 
=СУММ((D4:AH4="д")*D3:AH3) - формула массива.{/post}{/quote}  
 
Не, чтот не то, не работает в моей таблице... Здесь короче какая логика: Мне нужно что бы формула видела вместо букв цифры, которые в этой же формуле будут заданы. Тоесть мое видение примерно таково: СУММЕСЛИ "д+н" ТО "д=11,н=4" . Ну вот как то так. И так же примерно с ночами: СУММЕСЛИ "н" ТО "н=7"  
Привести теперь бы это в надлежащий вид))
 
 
 
#28
06.08.2011 03:32:17
День:  
=СУММ(СЧЁТЕСЛИ(D4:AH4;{"д";"н"})*{11;4})  
Ночь:  
=СЧЁТЕСЛИ(D4:AH4;"н")*7
 
 
 
#29
06.08.2011 03:37:44
{quote}{login=DV}{date=06.08.2011 03:32}{thema=}{post}День:  
=СУММ(СЧЁТЕСЛИ(D4:AH4;{"д";"н"})*{11;4})  
Ночь:  
=СЧЁТЕСЛИ(D4:AH4;"н")*7{/post}{/quote}  
 
То что надо!!!  
Респект!!! И ОГРОМНОЕ СПАСИБО!!!
 
 
 
#30
12.10.2011 11:27:41
Здравствуйте, тема подходит под мой случай (да простит меня ТС)  
Такая вот задачка, как суммировать только значения в ячейках, БЕЗ учета ошибок..
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх