Как получить значение из ячейки Excel

Обновлено: 20.11.2024

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

Например, если вы используете функцию Excel СЕГОДНЯ для вставки текущей даты в ячейку и оставляете ее в виде формулы, она будет обновляться, если вы откроете книгу на следующий день. Если это не то, что вам нужно, вам нужно преобразовать формулу в статическое значение.

Преобразование формул в значения в Excel

В этом руководстве я покажу вам различные способы преобразования формул в значения в Excel.

  • Использование сочетания клавиш.
  • Использование специальной техники вставки.
  • Использование крутого трюка с изгибанием мыши.
Использование сочетания клавиш

Это один из самых простых способов преобразования формул в значения. Простой трюк с сочетанием клавиш.

Вот как это сделать:

  • Выберите ячейки, для которых вы хотите преобразовать формулы в значения.
  • Скопируйте ячейки (Control + C).
  • Вставить как значения — сочетание клавиш — ALT + ESV

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

Использование специальной вставки

Если вы не являетесь поклонником сочетаний клавиш, вы можете использовать специальный параметр вставки, доступный на ленте в Excel.

Вот шаги по преобразованию формул в значения с помощью специальной вставки:

  • Выберите ячейки, для которых вы хотите преобразовать формулы в значения.
  • Скопируйте ячейки (Control + C).
  • На главную –> Буфер обмена –> Вставить –> Специальная вставка.

Вы также можете получить доступ к той же специальной опции вставки, если щелкнете правой кнопкой мыши скопированную ячейку. Наведите указатель мыши на параметр «Специальная вставка», и вы увидите все параметры внутри него.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel Starter 2010 Еще. Меньше

Функция ЯЧЕЙКА возвращает информацию о форматировании, расположении или содержимом ячейки. Например, если вы хотите убедиться, что ячейка содержит числовое значение вместо текста, прежде чем выполнять над ней вычисления, вы можете использовать следующую формулу:

Эта формула вычисляет A1*2, только если ячейка A1 содержит числовое значение, и возвращает 0, если ячейка A1 содержит текст или пуста.

Примечание. Формулы, в которых используется CELL, имеют значения аргументов для конкретного языка и возвращают ошибки, если вычисляются с использованием другой языковой версии Excel. Например, если вы создаете формулу, содержащую ЯЧЕЙКУ, при использовании чешской версии Excel, эта формула вернет ошибку, если книга будет открыта во французской версии. Если другим важно, чтобы ваша книга открывалась с использованием разных языковых версий Excel, рассмотрите возможность использования альтернативных функций или разрешения другим сохранять локальные копии, в которых они изменяют аргументы CELL, чтобы они соответствовали их языку.

Синтаксис

CELL(info_type, [ссылка])

Синтаксис функции CELL имеет следующие аргументы:

Описание

Текстовое значение, указывающее, какой тип информации о ячейке вы хотите вернуть. В следующем списке показаны возможные значения аргумента Info_type и соответствующие результаты.

Ячейка, о которой вы хотите получить информацию.

Если он опущен, информация, указанная в аргументе info_type, возвращается для ячейки, выбранной во время вычисления. Если ссылочным аргументом является диапазон ячеек, функция ЯЧЕЙКА возвращает информацию об активной ячейке в выбранном диапазоне.

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

В режиме автоматического вычисления, когда пользователь изменяет ячейку, вычисление может быть запущено до или после выбора, в зависимости от платформы, которую вы используете для Excel. Например, Excel для Windows в настоящее время инициирует расчет перед изменением выбора, но Excel для Интернета запускает его позже.

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

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

значения info_type

В следующем списке описаны текстовые значения, которые можно использовать для аргумента info_type. Эти значения должны быть введены в функцию ЯЧЕЙКА с кавычками (" ").

Ссылка на первую ячейку в ссылке в виде текста.

Номер столбца ячейки в ссылке.

Значение 1, если ячейка отформатирована в цвете для отрицательных значений; в противном случае возвращает 0 (ноль).

Примечание. Это значение не поддерживается в Excel для Интернета, Excel Mobile и Excel Starter.

Значение верхней левой ячейки ссылки; не формула.

Имя файла (включая полный путь) файла, содержащего ссылку, в виде текста. Возвращает пустой текст (""), если рабочий лист, содержащий ссылку, еще не сохранен.

Примечание. Это значение не поддерживается в Excel для Интернета, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее числовому формату ячейки. Текстовые значения для различных форматов показаны в следующей таблице. Возвращает "-" в конце текстового значения, если ячейка отформатирована в цвете для отрицательных значений. Возвращает "()" в конце текстового значения, если ячейка отформатирована со скобками для положительных или всех значений.

Примечание. Это значение не поддерживается в Excel для Интернета, Excel Mobile и Excel Starter.

Значение 1, если ячейка отформатирована со скобками для положительных или всех значений; в противном случае возвращает 0.

Примечание. Это значение не поддерживается в Excel для Интернета, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее "префиксу метки" ячейки. Возвращает одинарную кавычку ('), если ячейка содержит текст, выровненный по левому краю, двойную кавычку ("), если ячейка содержит текст, выровненный по правому краю, знак вставки (^), если ячейка содержит текст по центру, обратную косую черту (\), если ячейка содержит выровненный по заливке текст и пустой текст (""), если ячейка содержит что-либо еще.

Примечание. Это значение не поддерживается в Excel для Интернета, Excel Mobile и Excel Starter.

Значение 0, если ячейка не заблокирована; в противном случае возвращает 1, если ячейка заблокирована.

Примечание. Это значение не поддерживается в Excel для Интернета, Excel Mobile и Excel Starter.

Номер строки ячейки в ссылке.

Текстовое значение, соответствующее типу данных в ячейке. Возвращает "b" для пустого значения, если ячейка пуста, "l" для метки, если ячейка содержит текстовую константу, и "v" для значения, если ячейка содержит что-либо еще.

Возвращает массив из 2 элементов.

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

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

Примечание. Это значение не поддерживается в Excel для Интернета, Excel Mobile и Excel Starter.

Коды формата CELL

В следующем списке описаны текстовые значения, возвращаемые функцией ЯЧЕЙКА, когда аргументом Info_type является «формат», а аргументом ссылки является ячейка, отформатированная с использованием встроенного числового формата.

Совет. Попробуйте использовать новые функции XLOOKUP и XMATCH, улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их более простыми и удобными в использовании, чем их предшественники.

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

Функции ВПР и ГПР вместе с ИНДЕКС и ПОИСКПОЗ — одни из самых полезных функций в Excel.

Примечание. Мастер поиска больше не доступен в Excel.

Вот пример использования функции ВПР.

=ВПР(B2,C2:E7,3,ИСТИНА)

В этом примере B2 — это первый аргумент — элемент данных, необходимый для работы функции. Для ВПР этот первый аргумент — это значение, которое вы хотите найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «кузнец» или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором нужно искать значение, которое вы хотите найти. Третий аргумент — это столбец в этом диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент является необязательным. Введите ИСТИНА или ЛОЖЬ. Если вы введете TRUE или оставите аргумент пустым, функция вернет приблизительное соответствие значению, указанному вами в первом аргументе. Если вы введете FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, оставляя четвертый аргумент пустым или вводя значение TRUE, вы получаете больше гибкости.

В этом примере показано, как работает функция.Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает наиболее близкое приближенное совпадение из третьего столбца диапазона, столбца E (3-й аргумент).< /p>

Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. В противном случае вам пришлось бы вводить одно из значений в столбцах C или D, чтобы вообще получить результат.

Когда вы хорошо разбираетесь в функции ВПР, функция ГПР так же проста в использовании. Вы вводите те же аргументы, но поиск выполняется в строках, а не в столбцах.

Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР

Существуют определенные ограничения при использовании функции ВПР: функция ВПР может искать значение только слева направо. Это означает, что столбец, содержащий искомое значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если ваша электронная таблица не построена таким образом, не используйте функцию ВПР. Вместо этого используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.

В этом примере показан небольшой список, в котором значение, которое мы хотим найти, Чикаго, отсутствует в крайнем левом столбце. Итак, мы не можем использовать функцию ВПР. Вместо этого мы будем использовать функцию ПОИСКПОЗ, чтобы найти Чикаго в диапазоне B1:B11. Оно находится в строке 4. Затем ИНДЕКС использует это значение в качестве аргумента поиска и находит население Чикаго в 4-м столбце (столбец D). Используемая формула показана в ячейке A14.

Попробуйте

Если вы хотите поэкспериментировать с функциями поиска, прежде чем применять их к своим собственным данным, вот несколько примеров данных.

Пример VLOOKUP в работе

Скопируйте следующие данные в пустую электронную таблицу.

Совет. Прежде чем вставлять данные в Excel, задайте для столбцов с A по C ширину 250 пикселей и нажмите "Обтекание текстом" (вкладка "Главная", группа "Выравнивание").

Иногда ваши данные представляют собой несколько битов информации в одном столбце. Как столбец со штатами США в формате US-TX. Или столбец с компаниями и продаваемыми ими продуктами: Datawrapper (Software) .

Купите, скажите, что хотите, чтобы страна (США) была отделена от штата (Техас), например. для создания картограммы Datawrapper. Хорошо, что есть простые способы разделить точки данных на два или более столбца.

Я покажу два способа создания нескольких новых столбцов из одного столбца. Для этого мы будем использовать Google Таблицы, но это должно работать с LibreOffice Calc, Excel или любым другим программным обеспечением для работы с электронными таблицами.

Первый метод — это формула =SPLIT() :

Иногда у вас нет четких символов-разделителей, но вы просто хотите извлечь первый или последний символ ячейки. Для этого используйте формулы =ЛЕВОЕ(B1,2) , =ПРАВОЕ(B1,8) и =СРЕДНЕЕ(B1,2,4) :

  1. Вставьте новый столбец. (Или два. Или три! Сколько нужно.)
  2. В новых столбцах напишите
    =LEFT(B1,2), чтобы извлечь первые 2 символа ячейки B1.
    =ВПРАВО(B1,8), чтобы извлечь последние 8 символов ячейки B1.
    =MID(B1,4,2), чтобы извлечь 2 символа, следующие за 4-м символом в B1.
  3. Чтобы применить изменения к ячейкам ниже, перетащите синий квадрат вниз.

Советы профессионалов

Совет 1. Вы можете комбинировать формулы для извлечения символов в самых неожиданных местах. Например, формула =LEN() возвращает количество символов в ячейке. Таким образом, =LEFT(A1,LEN(A1)-2) извлекает весь текст в ячейке, кроме двух последних символов. Чтобы разделить ячейку Datawrapper (Software) на две ячейки Datawrapper и Software , вы можете использовать формулу =SPLIT(LEFT(A5,LEN(A5)-1),"(" . Эта формула сначала удаляет последнюю скобку, а затем разделяет оставшееся содержимое ячейки на ( .

Совет 2. Теперь, когда вы научились разделять текст, вы можете снова объединить его. Чтобы объединить столбец US из вашей ячейки A1 и TX из B1 с помощью дефиса, используйте амперсанд и напишите =A1&"-"&B1 .

Совет 3. Вы также можете извлекать содержимое с помощью LEFT() , RIGHT() и MID() не только из текстовых ячеек, но и из ячеек с числами и датами. Если вы хотите применить к своим датам такие формулы, как LEFT(), лучше сначала преобразовать их в текстовый формат. Для этого используйте формулу =ТЕКСТ(A1, "ММ/ДД/ГГГГ") . Вместо MM/DD/YYYY вы можете использовать любую комбинацию этих кодов даты и / , - , пробел и т. д. Например, =TEXT(A1, "dd-mmm-yyyy") преобразует формат даты 1st of Ноябрь 2019 г. в текстовую ячейку с содержанием 01 ноября 2019 г.

Совет 4. Если у вас есть пустые ячейки в столбце, и вы хотите, чтобы они были пустыми после того, как вы использовали, например, LEFT() , вам нужно сначала проверить эти пустые ячейки. Вы можете сделать это с помощью функции ЕПУСТО() в сочетании с функцией ЕСЛИ: =ЕСЛИ(ПУСТО(A1),"",ЛЕВО(A1,3)) .

Надеюсь, это было полезно! Если вам нужна дополнительная помощь в очистке данных, чтобы подготовить их для инструмента построения диаграмм, такого как Datawrapper, посетите нашу статью «Как подготовить данные для анализа и построения диаграмм в Excel и Google Sheets». А если у вас есть какие-либо вопросы, оставьте комментарий или напишите мне по адресу lisa@datawrapper.de.

(@lisacmuth, ранее Лиза Шарлотта Рост) отвечает за общение в Datawrapper, особенно за блог. Она много лет пишет о работе с данными и рада учиться и учить.

Читайте также: