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

Вход

Регистрация

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

 

= Мир MS Excel/Проверка ячеек на наличие в них формул - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверка ячеек на наличие в них формул (Формулы/Formulas)
Проверка ячеек на наличие в них формул
Juanvl Дата: Суббота, 21.05.2016, 15:25 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Привет. Подскажите, возможно ли чтобы при использовании функции "Если", в выбранном диапазоне, учитывались только те ячейки, которые не содержат формул? Как прописать такое условие?
Т.е. внешне все ячейки имеют цифры, но в одни ячейки формулы вбиты вручную, а в другие через формулы) Как учитывать только те ячейки, которые содержат цифры вбитые вручную? Спасибо.
 
Ответить
СообщениеПривет. Подскажите, возможно ли чтобы при использовании функции "Если", в выбранном диапазоне, учитывались только те ячейки, которые не содержат формул? Как прописать такое условие?
Т.е. внешне все ячейки имеют цифры, но в одни ячейки формулы вбиты вручную, а в другие через формулы) Как учитывать только те ячейки, которые содержат цифры вбитые вручную? Спасибо.

Автор - Juanvl
Дата добавления - 21.05.2016 в 15:25
Juanvl Дата: Суббота, 21.05.2016, 15:47 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Или можно условие по цвету прописать. Как учитывать, например, только красные ячейки в условии? Только красные суммировать
[moder]Файл с примером покажете?[/moder]
К сообщению приложен файл: 9211918.xlsx(9Kb)


Сообщение отредактировал Juanvl - Суббота, 21.05.2016, 16:22
 
Ответить
СообщениеИли можно условие по цвету прописать. Как учитывать, например, только красные ячейки в условии? Только красные суммировать
[moder]Файл с примером покажете?[/moder]

Автор - Juanvl
Дата добавления - 21.05.2016 в 15:47
buchlotnik Дата: Суббота, 21.05.2016, 15:54 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2195
Репутация: 646 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
и всё же - формулы или цвет? хотя если Excel 2007 - то только макросами


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщениеи всё же - формулы или цвет? хотя если Excel 2007 - то только макросами

Автор - buchlotnik
Дата добавления - 21.05.2016 в 15:54
Juanvl Дата: Суббота, 21.05.2016, 16:19 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
buchlotnik, да как угодно, цвет я предложил как альтернативу , т.к. и то и другое приведёт к одинаковому решению.
Файл для примера, по просьбе модератора.
К сообщению приложен файл: 6706931.xlsx(9Kb)
 
Ответить
Сообщениеbuchlotnik, да как угодно, цвет я предложил как альтернативу , т.к. и то и другое приведёт к одинаковому решению.
Файл для примера, по просьбе модератора.

Автор - Juanvl
Дата добавления - 21.05.2016 в 16:19
Manyasha Дата: Суббота, 21.05.2016, 16:35 | Сообщение № 5
Группа: Модераторы
Ранг: Старожил
Сообщений: 1721
Репутация: 722 ±
Замечаний: 0% ±

Excel 2007, 2010
Juanvl, udf-ка:
[vba]
Код
Function суммНеФормулы(r As Range)
    For Each cell In r
        If Left(cell.Formula, 1) <> "=" Then s = s + cell.Value
    Next cell
    суммНеФормулы = s
End Function
[/vba]
К сообщению приложен файл: 6706931-1.xlsm(15Kb)


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеJuanvl, udf-ка:
[vba]
Код
Function суммНеФормулы(r As Range)
    For Each cell In r
        If Left(cell.Formula, 1) <> "=" Then s = s + cell.Value
    Next cell
    суммНеФормулы = s
End Function
[/vba]

Автор - Manyasha
Дата добавления - 21.05.2016 в 16:35
Pelena Дата: Суббота, 21.05.2016, 16:48 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 10395
Репутация: 2353 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Для версий, начиная с Excel 2013 можно формулой
Код
=СУММПРОИЗВ(ЕНД(Ф.ТЕКСТ(B2:B12))*B2:B12)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеДля версий, начиная с Excel 2013 можно формулой
Код
=СУММПРОИЗВ(ЕНД(Ф.ТЕКСТ(B2:B12))*B2:B12)

Автор - Pelena
Дата добавления - 21.05.2016 в 16:48
buchlotnik Дата: Суббота, 21.05.2016, 16:52 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2195
Репутация: 646 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
функция как у Марины один в один B) [vba]
Код
Function SumNoFormula#(r)
    For Each c In r
        If Left(c.Formula, 1) <> "=" Then SumNoFormula = SumNoFormula + c
    Next
End Function
[/vba] ну а с 2013 можно и так:
Код
=СУММПРОИЗВ(B2:B12*(1-ЕФОРМУЛА(B2:B12)))
К сообщению приложен файл: 6706931.xlsm(15Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщениефункция как у Марины один в один B) [vba]
Код
Function SumNoFormula#(r)
    For Each c In r
        If Left(c.Formula, 1) <> "=" Then SumNoFormula = SumNoFormula + c
    Next
End Function
[/vba] ну а с 2013 можно и так:
Код
=СУММПРОИЗВ(B2:B12*(1-ЕФОРМУЛА(B2:B12)))

Автор - buchlotnik
Дата добавления - 21.05.2016 в 16:52
krosav4ig Дата: Суббота, 21.05.2016, 20:45 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1418
Репутация: 585 ±
Замечаний: 0% ±

Excel 2007, 2013
По секрету всему свету ...
у Range есть свойство .HasFormula
[vba]
Код
Function SumConstants(rng As Range)
    Dim cell As Range
    For Each cell In rng.Cells
        SumConstants = SumConstants - (Not cell.HasFormula) * cell
    Next
End Function
[/vba]


(_)Õvõ(_)
 
Ответить
СообщениеПо секрету всему свету ...
у Range есть свойство .HasFormula
[vba]
Код
Function SumConstants(rng As Range)
    Dim cell As Range
    For Each cell In rng.Cells
        SumConstants = SumConstants - (Not cell.HasFormula) * cell
    Next
End Function
[/vba]

Автор - krosav4ig
Дата добавления - 21.05.2016 в 20:45
buchlotnik Дата: Суббота, 21.05.2016, 21:04 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2195
Репутация: 646 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
[offtop]
Цитата
По секрету всему свету ...
чё ж по секрету-то? есть такое, но я не программист и мне простительно - тупо не люблю конструкции с Not или НЕ() - не знаю почему, ретроград B) вопрос по сути - а так оно реально быстрее?


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Суббота, 21.05.2016, 21:33
 
Ответить
Сообщение[offtop]
Цитата
По секрету всему свету ...
чё ж по секрету-то? есть такое, но я не программист и мне простительно - тупо не люблю конструкции с Not или НЕ() - не знаю почему, ретроград B) вопрос по сути - а так оно реально быстрее?

Автор - buchlotnik
Дата добавления - 21.05.2016 в 21:04
SLAVICK Дата: Суббота, 21.05.2016, 22:56 | Сообщение № 10
Группа: Модераторы
Ранг: Старожил
Сообщений: 1922
Репутация: 650 ±
Замечаний: 0% ±

2007,2010,2013,2016
а так оно реально быстрее?

Так оно реально правильнее :D
Вот две udf:
[vba]
Код
Function isFormula(r As Range) As Boolean
    isFormula = Left(r.Formula, 1) = "="
End Function
[/vba]
[vba]
Код
Function isFormula1(r As Range) As Boolean
    isFormula1 = r.HasFormula
End Function
[/vba]
могут показать разный результат :o
К сообщению приложен файл: 5399860.xlsm(13Kb)


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
а так оно реально быстрее?

Так оно реально правильнее :D
Вот две udf:
[vba]
Код
Function isFormula(r As Range) As Boolean
    isFormula = Left(r.Formula, 1) = "="
End Function
[/vba]
[vba]
Код
Function isFormula1(r As Range) As Boolean
    isFormula1 = r.HasFormula
End Function
[/vba]
могут показать разный результат :o

Автор - SLAVICK
Дата добавления - 21.05.2016 в 22:56
МВТ Дата: Воскресенье, 22.05.2016, 00:04 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 476
Репутация: 136 ±
Замечаний: 0% ±

Excel 2007
SLAVICK, здесь-то понятно: там текст в ячейке, а не формула. А вот почему такой вариант не работает, понять не могу :(
[vba]
Код
Function СУМ_КОНСТ(Rng As Range) As Double
    Set Rng = Rng.SpecialCells(xlCellTypeConstants, xlNumbers)
    СУМ_КОНСТ = WorksheetFunction.Sum(Rng)
End Function
[/vba]
 
Ответить
СообщениеSLAVICK, здесь-то понятно: там текст в ячейке, а не формула. А вот почему такой вариант не работает, понять не могу :(
[vba]
Код
Function СУМ_КОНСТ(Rng As Range) As Double
    Set Rng = Rng.SpecialCells(xlCellTypeConstants, xlNumbers)
    СУМ_КОНСТ = WorksheetFunction.Sum(Rng)
End Function
[/vba]

Автор - МВТ
Дата добавления - 22.05.2016 в 00:04
SLAVICK Дата: Воскресенье, 22.05.2016, 09:36 | Сообщение № 12
Группа: Модераторы
Ранг: Старожил
Сообщений: 1922
Репутация: 650 ±
Замечаний: 0% ±

2007,2010,2013,2016
вот почему такой вариант не работает, понять не могу

Читаем тут , или тут :D
Цитата
UDF action being ignored.
Excel will not allow a UDF written in VBA to alter anything except the value of the cell in which it is entered.
You cannotmake a VBA UDF which directly:

Alters the value or formula or properties of another cell.
Alters the formatting of the cell in which it is entered.
Alters the environment of Excel. This includes the cursor.
Uses FIND, SpecialCells, CurrentRegion, CurrentArray, GOTO, SELECT, PRECEDENTS etc : although you can use Range.End.
Note you can use FIND in Excel 2002/2003.


Иногда все проще чем кажется с первого взгляда.
 
Ответить
Сообщение
вот почему такой вариант не работает, понять не могу

Читаем тут , или тут :D
Цитата
UDF action being ignored.
Excel will not allow a UDF written in VBA to alter anything except the value of the cell in which it is entered.
You cannotmake a VBA UDF which directly:

Alters the value or formula or properties of another cell.
Alters the formatting of the cell in which it is entered.
Alters the environment of Excel. This includes the cursor.
Uses FIND, SpecialCells, CurrentRegion, CurrentArray, GOTO, SELECT, PRECEDENTS etc : although you can use Range.End.
Note you can use FIND in Excel 2002/2003.

Автор - SLAVICK
Дата добавления - 22.05.2016 в 09:36
Juanvl Дата: Воскресенье, 22.05.2016, 11:59 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 44
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо!!!! С чем бы к сюда ни обращался,всегда выручаете)
А для решения текущей ситуации, поищу 2013 версию, давно пора))


Сообщение отредактировал Juanvl - Воскресенье, 22.05.2016, 12:52
 
Ответить
СообщениеСпасибо!!!! С чем бы к сюда ни обращался,всегда выручаете)
А для решения текущей ситуации, поищу 2013 версию, давно пора))

Автор - Juanvl
Дата добавления - 22.05.2016 в 11:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Проверка ячеек на наличие в них формул (Формулы/Formulas)
Страница 1 из 11
Поиск:

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