识别一个单元格中的两个数字是都是奇数或都是偶数。

huangapple go评论66阅读模式
英文:

Identify two digits in a cell are both odd or both even

问题

我需要您在以下方面的帮助:

我有许多以两位数字格式表示的记录。
对于这些记录,我想验证两个单独的数字是否都是偶数或都是奇数。

目的是设置条件格式规则,一个用于两者都是奇数,另一个用于两者都是偶数。

请查看下面的示例数据:

13 29 91 98 34 42
32 16 18 57 80 20
02 02 51 31 64 13
50 25 47 54 32 41

正如您所看到的,有些数字被标记为粗体。这些数字由2个奇数数字组成;
其他数字被标记为斜体。这些数字由2个偶数数字组成。

示例:

1313 组成;这两个数字都是奇数。这应该被格式化为红色(例如)。

2929 组成;2 是偶数,但 9 是奇数。这不应该被格式化。

4242 组成;这两个数字都是偶数。这应该被格式化为绿色(作为示例)。

我不知道如何分开检查同一单元格内的单个数字。

英文:

I need your help on the following:

I have a number of records in two digit format.
For these I want to verify for the two separate digits to either both be even, or both be odd.

The purpose is to have a conditional formatting rule set for both being odd and another for both being even.

Please find some sample data below:

13 29 91 98 34 42
32 16 18 57 80 20
02 02 51 31 64 13
50 25 47 54 32 41

As you can see some numbers are marked bold. These numbers consist of 2 odd digits;
others are marked italic. These consist of 2 even digits.

Example:

13 consist of 1 and 3; both these numbers are odd. This should be formatted red (for example).

29 consists of 2 and 9; 2 being even, but 9 being odd. This should not be formatted.

42 consists of 4 and 2; both being even numbers. This should be formatted green (as an example)

I have no clue how to separate the check of the single digits within the same cell.

答案1

得分: 4

cell中的两个数字都是奇数

这个公式检查单元格中的第一个和第二个数字是否都是奇数:
=AND(ISODD(--MID(A1,ROW($1:$2),1)))

或者更简洁的方式,正如VBasics2008建议的:
=AND(ISODD(--MID(A1,{1;2},1)))

cell中的两个数字都是奇数或者都是偶数

在你的问题中,你对同时包含两个奇数数字和同时包含两个偶数数字的情况进行了格式化。在这种情况下,使用=SUM(MOD(--MID(A1,ROW($1:$2),1),2))<>1

=SUM(MOD(--MID(A1,{1;2},1),2))<>1

cell中的两个数字都是偶数

如果你想要分别检查两个数字都是偶数的情况,使用=AND(ISEVEN(--MID(A1,ROW($1:$2),1)))

=AND(ISEVEN(--MID(A1,{1;2},1)))

英文:

both digits in cell are odd

This checks the first and second digit in a cell to be odd:
=AND(ISODD(--MID(A1,ROW($1:$2),1)))

Or shorter, as suggested by VBasics2008 (thanks!):
=AND(ISODD(--MID(A1,{1;2},1)))

2 digits in cell are both either odd or even

In your question you formatted both numbers that had both digits odd and also the ones that had both even. In that case use =SUM(MOD(--MID(A1,ROW($1:$2),1),2))<>1

Or =SUM(MOD(--MID(A1,{1;2},1),2))<>1

Both digits in cell are even

If you want a separate for both being even, use =AND(ISEVEN(--MID(A1,ROW($1:$2),1)))

Or =AND(ISEVEN(--MID(A1,{1;2},1)))

答案2

得分: 2

如果我理解你的意思正确,这应该可以用来检查两个数字是奇数还是偶数:

=OR(AND(ISEVEN(QUOTIENT(A1,10)),ISEVEN(MOD(A1,10))),AND(ISODD(QUOTIENT(A1,10)),ISODD(MOD(A1,10))))

解释:

  • A1 在这里是包含两位数字的单元格。
  • 通过 QUOTIENTMOD 将数字除以10,以分开各自的数字。
  • 使用 ISEVENISODD 根据条件进行检查。

编辑:

一个更短的版本来检查两个数字都是奇数还是都是偶数:

=ISEVEN(SUM(QUOTIENT(A1,10),MOD(A1,10)))

编辑2:

使用第一个公式的相应 AND() 部分单独检查偶数或奇数(之前错过了你的评论)

顺便说一下,这个解决方案不需要对数字进行任何字符串操作。

英文:

If I understand you correctly, this should work to check both digits are odd or even:

=OR(AND(ISEVEN(QUOTIENT(A1,10)),ISEVEN(MOD(A1,10))),AND(ISODD(QUOTIENT(A1,10)),ISODD(MOD(A1,10))))

Explanation:

  • A1 is here the cell with the 2 digit number
  • Just divide the number by 10 via QUOTIENT and MOD to separate the
    respective digits.
  • check the conditions with ISEVEN and ISODD accordingly

Edit:

A shorter version to check both digits are odd or both are even:

=ISEVEN(SUM(QUOTIENT(A1,10),MOD(A1,10)))

Edit2:

Use the respective AND() sections of the first formula to check for even or odd separately (missed your comment earlier)

btw, this solution does not require any string operation on the number

答案3

得分: 1

你在评论中写道:“如果两个数字都是奇数,则颜色为红色,如果两个数字都是偶数,则颜色为绿色”。

所以你需要两个条件格式规则。一个是为红色,一个是为绿色。

当两个数字都是奇数时,下面的公式为真:

=(MOD(LEFT(TEXT(A1,"00"),1),2) + MOD(RIGHT(TEXT(A1,"00"),1),2))=2

当两个数字都是偶数时,下面的公式为真:

=AND(A1<>"",(MOD(LEFT(TEXT(A1,"00"),1),2) + MOD(RIGHT(TEXT(A1,"00"),1),2))=0)

根据你的本地设置,使用逗号替换分号。我修改了偶数的公式,使其在空单元格上返回FALSE并保持没有颜色。这些公式在空单元格上不会出错。

第二种方法:(如果单元格为空,则都返回FALSE)

如果数字为奇数,则为真:

=MOD(INT(A1/10),2)+MOD(A1,2)=2

如果数字为偶数,则为真:

=AND(A1<>"",MOD(INT(A1/10),2)+MOD(A1,2)=0)
英文:

You wrote in comments: "if both digit odd then color red or both digit even color green"

So you need two conditional formating rules. One for the red and one for the green.

WHEN THE TWO DIGITS ARE ODD THE NEXT FORMULA IS TRUE

=(MOD(LEFT(TEXT(A1;&quot;00&quot;);1);2) + MOD(RIGHT(TEXT(A1;&quot;00&quot;);1);2))=2

WHEN THE TWO DIGITS ARE EVEN THE NEXT FORMULA IS TRUE

=AND(A1&lt;&gt;&quot;&quot;;(MOD(LEFT(TEXT(A1;&quot;00&quot;);1);2) + MOD(RIGHT(TEXT(A1;&quot;00&quot;);1);2))=0)

Use them accordingly. Change the ; with , according your local settings.
I modify the even formula to return FALSE on empty cells and remain without color. The formulas don't returm error on empty cells.

A second approach: (If cell is empty both return false)

true if digits are odd:

=MOD(INT(A1/10);2)+MOD(A1;2)=2

true if digits are even:

=AND(A1&lt;&gt;&quot;&quot;;MOD(INT(A1/10);2)+MOD(A1;2)=0)

huangapple
  • 本文由 发表于 2023年6月19日 01:06:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/76501691.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定