Ян должен ввести хронологический возраст ученика в первом столбце в формате 9,11 для девяти лет и одиннадцати месяцев, возраст чтения в следующий столбец (скажем, 10,6 для десяти лет и шести месяцев), затем вычислить разницу в месяцев в третьем столбце, который в данном случае будет 7. Разницу всегда нужно указывать в месяцах, поэтому промежуток должен быть равен 14 месяцам вместо 1,2 или 24 месяца вместо 2,0.

Ян не знает, как составить такую ​​формулу.

Уловка состоит в том, чтобы просто преобразовать в общий набор единиц, в данном случае месяцев. Так, например, 10,6 будет 12 * 10 + 6 или 126 месяцев. Если вы сделаете этот тип преобразования и в хронологический возраст, и в возраст чтения, то вы сможете произвести любое необходимое вычитание, чтобы определить разницу, опять же в месяцах.

Однако здесь есть серьезное предостережение: то, как вы вводите нестандартные даты в рабочий лист, будет иметь огромное влияние на то, как вы работаете с этими данными. Проблема лучше всего проиллюстрирована рассмотрением значения даты, например 10.10, что означает возраст 10 лет и 10 месяцев. Если вы введете это значение непосредственно в ячейку, Excel проанализирует его как числовое значение и автоматически изменит его на 10,1, поскольку конечный 0 (для Excel) не имеет значения. К сожалению, это значение неотличимо от 10,1, что означает 10 лет и 1 месяц.

Есть два возможных решения. Если вы хотите ввести числовые значения, вы должны всегда включать начальный 0 для месяцев.

Таким образом, вы должны ввести 10.01 или 10.06, а не 10.1 или 10.6.

Однако в постановке задачи Иен специально использует 10.6 в качестве примера, что означает, что ведущие нули не вводятся. В этом случае вы должны убедиться, что то, что вы вводите, обрабатывается Excel как текст. Другими словами, убедитесь, что вы форматируете эти столбцы ввода даты как текст перед началом ввода дат. Таким образом, Excel будет отображать 10.10 именно так с нулевым в конце.

Все решения, которые вы используете для расчетов, предполагают, что вы вводите даты с нулем в начале для месяцев или что вы вводите даты как текстовые значения. Во всех случаях предположим, что хронологический возраст указан в столбце A, а возраст чтения — в столбце B.

Если ваши даты отформатированы как текст, вы можете использовать следующую формулу в столбце C:

=INT(B1)12+MID(B1,FIND(".",B1)+1,2)-(INT(A1)12+MID(A1,FIND(".",A1)+1,2))

Он возвращает положительное значение, если возраст чтения превышает хронологический возраст, или отрицательное значение, если возраст чтения меньше хронологического возраста. Вы не можете использовать формулу, если даты вводятся как числовые значения, потому что, если возраст вводится как 10,00 (то есть 10 лет и 0 месяцев), Excel все равно анализирует это значение как 10.

Затем формула возвращает # ЗНАЧ! ошибка, потому что функция НАЙТИ не может найти несуществующую десятичную точку.

Если вы используете даты в числовом формате (опять же, с ведущими нулями для месяцев), вы можете полагаться на любую из этих двух формул:

=12(INT(A1)-INT(B1))+100(MOD(A1,1)-MOD(B1,1))

=(DOLLARDE(A1,12)-DOLLARDE(B1,12))*12

Ключ должен быть последовательным в том, как форматируются значения даты, использовать начальные нули для месяцев при вводе в числовом формате и использовать правильную формулу в зависимости от того, работаете ли вы с текстовыми или числовыми значениями.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10095) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.