Как перевести ложь и истина в 0 и 1 excel

Обратный порядок элементов в списке

Банальная, на первый взгляд, задача, периодически встречающаяся в работе почти любого пользователя Microsoft Excel – расположить элементы списка в обратном порядке. При всей кажущейся простоте, здесь есть свои «фишки» — давайте разберем несколько вариантов ее решения.

Способ 1. Ручная сортировка по доп.столбцу

Это обычно первое, что приходит в голову. Добавляем рядом с нашим списком еще один столбец с порядковыми номерами и сортируем по этому столбцу по убыванию:

Очевидный плюс такого подхода в простоте. Очевидный же минус в том, что нужно руками проделать энное количество операций. Если это разовая задача — ОК, но если данные меняются каждый день, то сортировать список постоянно вручную уже напрягает. Выходом может стать использование формул.

Способ 2. Обратный порядок формулой

Поскольку формулы в Excel пересчитываются автоматически (если включен ручной режим пересчета), то и сортировка, реализованная формулами, будет происходить «на лету», без какого либо участия пользователя. Нужная нам формула, размещающая элементы списка в обратном порядке может выглядеть так:
Недостаток этой формулы в том, что в ней должны жестко задаваться начало и конец списка (ячейки A2 и A9 в нашем случае). Если заранее точно не известно, сколько именно элементов будет в списке, то лучше использовать другой подход: В этой формуле номер последней занятой ячейки подсчитывается с помощью функции СЧЁТЗ (COUNTA) , т.е. количество элементов в исходном списке может впоследствии меняться. Минус этого варианта — в исходном списке не должно быть пустых ячеек, т.к. функция СЧЁТЗ тогда неправильно вычислит номер строки последнего элемента. Выходом может стать использование динамического именованного диапазона с автоподстройкой размеров либо хитрой формулы массива:
Как легко заметить, это вариация первого способа, где диапазон взят «с запасом» сразу до сотой строки и номер строки последней заполненной ячейки задается не жестко, а вычисляется с помощью фрагмента МАКС(($A$2:$A$100<>«»)*СТРОКА($A$2:$A$100)) Каждая ячейка в диапазоне A2:A100 проверяется на заполненность с помощью выражения ($A$2:$A$100<>«»), что даст на выходе массив значений ИСТИНА и ЛОЖЬ. Затем этот массив поэлементно умножается на массив номеров строк, получаемый с помощью функции СТРОКА($A$2:$A$100). Поскольку логическую ИСТИНУ Excel интерпретирует как 1, а ЛОЖЬ – как 0, то после умножения мы получим массив номеров заполненных ячеек. А уже из него функция МАКС (MAX) выбирает самое большое число, т.е. номер последней заполненной строки. И, само-собой, не забудьте после ввода этой формулы нажать не обычный Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести ее как формулу массива.

Способ 3. Макрос

Если хочется реализовать перекладывание значений ячеек в обратном порядке без дополнительного столбца с формулами, т.е. прямо в исходных ячейках, то не обойтись без простого макроса.
Нажмите сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) . Вставьте новый пустой модуль через меню Insert — Module и скопируйте туда текст макроса:

Sub Reverse() Dim arrData(), n As Long arrData = Selection For Each cell In Selection cell.Value = arrData(UBound(arrData) - n, 1) n = n + 1 Next cell End Sub

Теперь, если выделить столбец-список с данными и запустить наш макрос с помощью сочетания Alt+F8 или команды Разработчик — Макросы (Developer — Macros) , то список развернется в обратном порядке прямо в тех же ячейках, т.е. на месте.

Ссылки по теме

• Как создать в Excel динамический именованный диапазон с автоподстройкой размеров
• Что такое формулы массива и как их использовать
• Хитрости сортировки в MS Excel

Как перевести ложь и истина в 0 и 1 excel

Хочу рассказать о своей небольшой находке, связанной с логическими формулами. Безусловно, если начать искать, то окажется, что я буду далеко не первым, кто так делает, поэтому ни о каком авторстве тут, конечно, речи идти не может.

Часто в свои файлы Excel приходится вживлять множество формул, которые могут принимать 2 значения, противоположных друг другу. Например, мне часто требуется иметь набор ячеек для столбцов/строк, которые бы хранили статус их (строк/столбцов) видимости. Скажем, если A5 (люблю под это выделять первый столбец) содержит 1, то строка 5:5 должна быть видна и, если VBA программа обнаружит, что строка скрыта, то она сделает её видимой, и — наоборот.

Для таких вещей в Excel есть логический (булевый) тип, который состоит из двух констант: ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Существуют также логические выражения, которые возвращают логический тип, как результат своего вычисления. Возвращаясь к примеру про видимость строки 5:5 , в A5 могла бы находиться такая формула:

= D5 <> «»

при этом, если D5 не пуста, то она вернёт значение ИСТИНА , в противном случае ЛОЖЬ .

Можно было бы так и оставить, но уж больно эти константы занимают много места на экране, поэтому лично мне с эстетической точки зрения больше нравится в подобных ячейках иметь значения 1 или 0. Традиционно этого добиваются так:

= ЕСЛИ ( D5 <> «» ; 1 ; 0 )

Однако, мне в последнее время нравится такой способ:

= ( D5 <> «» ) + 0

Данный метод имеет более лаконичную форму и, полагаю, чуть быстрее вычисляется. Обратите внимание, что скобки тут необходимы, в противном случае порядок вычислений будет нарушен. Excel в начале вычисляет логическое выражение в скобках, а затем вынуждено его преобразовать в число, так как завершающая операция — операция сложения. Как известно значение ИСТИНА преобразуется в 1, а ЛОЖЬ — в 0, поэтому прибавление нуля тут ничего изменить не может (впрочем как и умножение на 1, возведение в степень 1, вычитание 0, деление на 1).

Кстати, то обстоятельство, что у меня в A5 теперь числа (0 или 1), а не логические константы ( ЛОЖЬ или ИСТИНА ), никак не повлияет на возможность использования ячейки A5 в логических операторах. Точно так же будут отлично работать такие формулы:

= ЕСЛИ ( A5 ; «Видима» ; «Скрыта» )

= И ( A5 ; A1 > 0 )

Это происходит, так как 1 преобразуется в ИСТИНА , а 0 — в ЛОЖЬ . На самом деле в ИСТИНА будет преобразовано ЛЮБОЕ ЧИСЛОВОЕ значение, отличное от нуля. Любое!

Кстати последнюю формулу так же очень удобно переводить в числовую форму:

= И ( A5 ; A1 > 0 ) + 0

и дополнительных скобок уже не потребуется, достаточно тех, что уже есть у оператора И (AND).

Вот такая небольшая хитрость 🙂

покупка

Например, у вас есть столбец, заполненный «ИСТИНА» и «ЛОЖЬ» в опросе, и вы хотите преобразовать значение true в поле с установленным флажком и преобразовать значение false в поле без флажка, как вы могли бы справиться с этим в Excel? В этой статье представлены некоторые решения для вас.

• Преобразовать true false в флажок в Excel
• Преобразование true false в элементы управления флажками с помощью VBA
• Превратите true false в элементы управления флажками с помощью удивительного инструмента
• Еще статьи о вставке, форматировании и редактировании флажков .

Преобразовать true false в флажок в Excel

Предположим, вы указали числа в двух столбцах, если число в первом столбце больше, чем во втором столбце, отобразите ИСТИНА, в противном случае отобразите Ложь, как показано ниже. Теперь вы можете изменить формулу, чтобы легко преобразовать ИСТИНА или ЛОЖЬ в флажки в Excel.

Измените исходную формулу на = ЕСЛИ (исходная_формула, «þ», «¨»)

1. В этом случае я использую формулу = B3> C3 для сравнения чисел в двух столбцах. Поэтому я изменю исходную формулу, как показано ниже, а затем перетащу маркер автозаполнения в другие ячейки.

= ЕСЛИ (B3> C3, «þ», «¨»)

2. Не снимая выделения с ячеек формулы, щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню.

3. В диалоговом окне Формат ячеек перейдите к шрифт вкладка, выберите Обмотки из шрифт список и щелкните OK кнопку.

Теперь вы увидите, что специальные символы преобразованы в отмеченные флажки и не отмеченные флажки. Смотрите скриншот:

Заметки:

• Если вы вводите текст «True» или «False» вручную (ячейки должны быть заранее отформатированы как текст), вы можете изменить их на флажки с помощью этой формулы = ЕСЛИ (D3 = «Истина», «þ», «¨»).
• Флажки автоматически изменятся в зависимости от возвращаемых значений исходных формул.

Преобразование true false в элементы управления флажками с помощью VBA

В этом разделе будет представлен макрос VBA для преобразования нескольких значений true и false в элементы управления флажками в Excel. Пожалуйста, сделайте следующее:

1. Выберите ячейки, равные «ИСТИНА» и «ЛОЖЬ», которые вы преобразуете в флажки.

2, нажмите другой + F11 вместе, чтобы открыть окно Microsoft Visual Basic для приложений.

3. Нажмите Вставить > Модули, а затем вставьте ниже код VBA в новое окно модуля.

VBA: преобразование ИСТИНА и ЛОЖЬ в элементы управления флажками в Excel

Sub ConvertTrueFalseToCheckbox() Dim xCB As CheckBox Dim xRg, xCell As Range Application.ScreenUpdating = False On Error Resume Next Set xRg = Selection For Each xCB In ActiveSheet.CheckBoxes xCB.Delete Next For Each xCell In xRg If (UCase(xCell.Value) = "TRUE") Or (UCase(xCell.Value) = "FALSE") Then Set xCB = ActiveSheet.CheckBoxes.Add(xCell.Left, xCell.Top, cDblCheckboxWidth, xCell.Height) xCB.Value = xCell.Value xCB.LinkedCell = xCell.Address xCB.Text = "" End If Next Application.ScreenUpdating = True End Sub

4, нажмите F5 ключ или щелкните Run кнопку для запуска этого VBA.

Затем вы увидите, что все выбранные ИСТИНА и ЛОЖЬ преобразованы в элементы управления флажками. ИСТИНА преобразуется в поле с установленным флажком, а ЛОЖЬ — в поле без флажка. Смотрите скриншот:

Ноты:

• Вы можете установить или снять эти флажки по своему усмотрению. Если вы установите флажок FALSE, FALSE автоматически изменится на TRUE, и наоборот.
• Если вы примените этот VBA к ячейкам формулы (которые возвращают ИСТИНА и ЛОЖЬ), флажки автоматически изменятся в зависимости от возвращаемых значений формул. Однако после того, как вы вручную отметили или сняли эти флажки, они не изменятся в зависимости от возвращаемых значений формулы.

Превратите true false в элементы управления флажками с помощью удивительного инструмента

Если вы только что Kutools for Excel установлен, вы можете применить его Флажки пакетной вставки функция для массовой вставки нескольких флажков одним щелчком мыши в Excel.

Kutools for Excel— Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 60-дневная пробная версия, кредитная карта не требуется! Get It Now

1. Выберите ячейки, равные «ИСТИНА» и «ЛОЖЬ», которые вы преобразуете в флажки, и нажмите Кутулс > Вставить > Флажки пакетной вставки.

2. Теперь в каждую выбранную ячейку вставлен флажок. Вы можете вручную установить эти флажки по мере необходимости.

Статьи по теме:

Как создать флажки «Да» или «Нет» в Excel
Если вам нужно создать список флажков Да / Нет и сделать их взаимоисключающими друг для друга, вам может помочь метод, описанный в этой статье.

Скрыть истинные / ложные слова в ячейке при установке флажка в Excel
После связывания флажков с ячейками вы можете видеть слова ИСТИНА или ЛОЖЬ, отображаемые в связанных ячейках, при установке или снятии флажков, как показано ниже. Если вы хотите скрыть эти отображаемые слова, попробуйте метод, описанный в этой статье.

Установите флажок на основе значения ячейки в Excel
Возможно, вы знаете, как изменить значение ячейки на основе флажка. Но знаете ли вы, как автоматически установить флажок на основе значения ячейки в Excel? Эта статья покажет вам, как этого добиться.

Удалить несколько флажков в Excel
Возможно, у вас есть лист с множеством флажков, и теперь вы хотите их удалить. Есть ли какие-нибудь быстрые способы удалить их, кроме удаления их по одному вручную?

Преобразование чисел-как-текст в нормальные числа

Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:
Причем иногда такой индикатор не появляется (что гораздо хуже). В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:

• перестает нормально работать сортировка — «псевдочисла» выдавливаются вниз, а не располагаются по-порядку как положено:
• функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
  
• при фильтрации псевдочисла отбираются ошибочно
• многие другие функции Excel также перестают нормально работать:
• и т.д.

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК — и ничего не происходит! Совсем!

Возможно, «это не баг, а фича», конечно, но нам от этого не легче. Так что давайте-к рассмотрим несколько способов исправить ситуацию — один из них вам обязательно поможет.

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

Все числа в выделенном диапазоне будут преобразованы в полноценные.

Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:

Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.

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

Способ 4. Специальная вставка

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

• в любую пустую ячейку введите 1
• скопируйте ее
• выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
• щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
• в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

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

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Sub Convert_Text_to_Numbers() Selection.NumberFormat = "General" Selection.Value = Selection.Value End Sub

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) — и моментально преобразовать псевдочисла в полноценные.

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

P.S.

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Ссылки по теме

• Деление слипшегося текста по столбцам
• Вычисления без формул специальной вставкой
• Преобразование текста в числа с помощью надстройки PLEX