Как присвоить значение ячейке в Excel

Обновлено: 21.11.2024

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

Допустим, Стив выиграл 3 из 6 соревнований по навыкам в понедельник. Каждая стоит 10 долларов. Я хочу иметь возможность ввести имя Стива в Excel для событий, которые он выиграл, а затем автоматически подсчитать промежуточный итог для меня в другой ячейке.

Добавьте еще один столбец с суммой в долларах для каждого события, а не в заголовке, как у вас. затем используйте СУММЕСЛИМН()

Спасибо, Скотт. Я добавил столбец значений справа, как вы предложили (где написано воскресенье). Где я могу войти в SUMIF? Извините, я ничего не знаю об Excel.

3 ответа 3

С небольшой настройкой макета вы можете использовать SumIf()+SumIf().

По сути, вы SumIf([воскресный диапазон],[имя],[долларовый призовой ряд]) и просто добавляете еще одно значение для понедельника, затем вторника и т. д.

Намного проще использовать формулу СУММПРОИЗВ.

При использовании приведенного выше нового макета BruceWaynes формула в каждой ячейке столбца K будет выглядеть следующим образом (где n – номер строки, в которую вы вводите формулу)

Стив будет =СУММПРОИЗВ((B:H=J16)*(B:B))

Крис будет =СУММПРОИЗВ((B:H=J17)*(B:B))

Я знал, что, вероятно, есть более простой способ, и подозревал, что SumProduct() будет именно таким. Я просто не слишком хорошо это знаю. Хорошая мысль!

Я идиот. Я не могу понять это. Страховые вопросы. Я готов идти, Excel .. невежественный. Что я делаю неправильно? Могу ли я отправить кому-нибудь электронное письмо?

Посмотрим, правильно ли я понял ваш вопрос. Допустим, Стив выиграл 3 турнира по 10 долларов каждый. Теперь я не совсем уверен, какие ячейки представляют эти события, поэтому я просто приведу свои собственные примеры.

Для этого можно использовать функцию СЧЁТЕСЛИ(диапазон, критерии), а также функцию СУММЕСЛИМН()

Скажем, вы отмечаете тот факт, что Стив выигрывает ежедневный lf в понедельник, вводя Стива в C17 или подобное.

Затем в ячейке "Всего" за Стивом H16 (или где угодно) вы можете ввести что-то вроде:

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

Затем будет подсчитано количество слов "Стив" и умножено это число на 10.

Если вы хотите, чтобы разные события имели разные значения, и предположим, что ваши значения находятся в столбце B, а все столбцы смещены вправо, вы можете легко изменить это на что-то вроде:

А затем перетащите его вниз, чтобы закрыть каждое имя. Минус — довольно длинная функция, с которой не так просто работать. Вы также можете сделать это без столбца B и иметь значения непосредственно в функции, как в первом примере.

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

Загрузить книгу

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

В этом наборе данных я попытался представить реальный пример. Здесь представлены два вида напитков: Чипсы и Холодные напитки. В единственном столбце под названием «Все продукты» название и категория напитков связаны друг с другом. В зависимости от входных критериев значения будут присвоены столбцам C, D и E.

Разбивка функций, используемых в этой статье

В формулах, которые я здесь использовал, используются следующие функции:

<р>1. Функция СЧЁТЕСЛИМН:

Эта функция подсчитывает ячейки, соответствующие нескольким критериям. Синтаксис функции СЧЁТЕСЛИМН следующий.

range1 — первый диапазон для оценки.

criteria1 — критерии для диапазона 1.

range2 [необязательно]: второй диапазон для оценки.

criteria2 [необязательно]: критерии для диапазона2. Допускается использование до 127 пар "диапазон/критерий".

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

<р>2. Функция ПОИСК:

Эта функция получает местоположение текста в строке. Синтаксис функции ПОИСК следующий.

find_text: текст для поиска.

within_text: текст для поиска.

start_num [необязательно]: начальная позиция в тексте для поиска. Необязательно, по умолчанию 1.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

<р>3. Функция НАЙТИ:

Эта функция получает местоположение текста в строке. Синтаксис функции НАЙТИ следующий.

find_text: текст для поиска.

within_text: текст для поиска.

start_num [необязательно]: начальная позиция в тексте для поиска. Необязательно, по умолчанию 1.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

<р>4. Функция ВПР:

Эта функция ищет значение в таблице путем сопоставления с первым столбцом. Синтаксис функции ВПР следующий.

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

table: таблица, из которой нужно получить значение.

col_index: столбец в таблице, из которого извлекается значение.

range_lookup [необязательно]: TRUE = приблизительное совпадение (по умолчанию). ЛОЖЬ = точное совпадение.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

<р>5. Функция IЧИСЛО:

Эта функция проверяет числовые значения. Синтаксис функции ЕЧИСЛО следующий.

значение: значение для проверки.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

<р>6. Функция ЕСЛИОШИБКА:

Эта функция перехватывает и обрабатывает ошибки. Синтаксис функции ЕСЛИОШИБКА следующий.

значение: значение, ссылка или формула для проверки на наличие ошибки.

value_if_error: возвращаемое значение в случае обнаружения ошибки.

Подробнее об этой функции можно узнать, прочитав эту документацию Microsoft.

4 формулы для присвоения значения, если ячейки содержат слово

1. Использование функции СЧЁТЕСЛИ

Здесь я проверил входные критерии в столбце "Все продукты" и заполнил столбцы C, D и E в соответствии с ними.

В столбце C я вернул значения ячеек столбца All products, соответствующие критериям Chips.

Формула выглядит следующим образом:

=> ЕСЛИ(ИЛИ(СЧЁТЕСЛИ($B9,"*Чипы*")), B9, "Не найдено")

[Здесь знак звездочки (*) является подстановочным знаком. Он искал подстроку «Chips» в ячейке B9, которая является строкой «Ruffles — Chips».]

[Функция СЧЁТЕСЛИ возвращает единицу для каждого совпадения подстроки. Поскольку слово «фишки» находится в ячейке B9, возвращается 1.]

[Поскольку значение функции ЕСЛИ равно единице (1)=ИСТИНА, она возвращает первый аргумент, который является желаемым результатом.]

В столбце D я вернул ИСТИНА или ЛОЖЬ на основе критериев фишек

Используется та же предыдущая формула, за исключением того, что здесь изменен вывод функции ЕСЛИ.

В столбце E я вернул 1 или 0 на основе критерия фишек

Используется та же предыдущая формула, за исключением того, что здесь изменен вывод функции ЕСЛИ.

Я также подсчитал общее количество ячеек, соответствующих критериям, и показал его ячейке C18.

Формула выглядит следующим образом:

[Здесь знак звездочки (*) является подстановочным знаком. Он искал подстроку «Chips» в диапазоне ячеек B9:B15.]

[Функция СЧЁТЕСЛИ возвращает единицу для каждого совпадения подстроки. Поскольку в диапазоне ячеек B9:B15 есть четыре ячейки, содержащие критерий «Фишки», возвращается 1.]

2. Использование функции ПОИСК

Здесь я проверил входные критерии в столбце "Все продукты" и заполнил столбцы C, D и E в соответствии с ними.

В столбце C я вернул значения ячеек столбца All products, соответствующие критериям Chips.

Формула выглядит следующим образом:

[Функция ПОИСК выполнила поиск значения входных критериев в ячейке B7. Для «Фишки» он вернул 11, что является начальной позицией подстроки.]

[Функция ЕЧИСЛО преобразовала 11 в значение ИСТИНА.]

[Поскольку значение функции ЕСЛИ равно ИСТИНА, она возвращает первый аргумент, который является желаемым результатом.]

В столбце D я вернул ИСТИНА или ЛОЖЬ на основе критериев фишек.

Используется та же предыдущая формула, за исключением того, что здесь изменен вывод функции ЕСЛИ.

В столбце E я вернул 1 или 0 на основе критерия фишек.

Используется та же предыдущая формула, за исключением того, что здесь изменен вывод функции ЕСЛИ.

Я также подсчитал общее количество ячеек, соответствующих критериям Chips. Вы можете проверить первый метод, чтобы получить полное объяснение.

3. Использование функции НАЙТИ

Эта формула аналогична формуле функции ПОИСК, за исключением того, что в ее основе используется функция ПОИСК вместо функции ПОИСК.

Формула выглядит следующим образом:

[Функция НАЙТИ выполнила поиск значения входных критериев в ячейке B7 и вернула местоположение. Для «Фишки» было возвращено 11, что является начальной позицией подстроки.]

[Функция ЕЧИСЛО преобразовала 11 в значение ИСТИНА.]

Вы можете изменить выходные данные, просто изменив аргументы функции ЕСЛИ в формуле.

4. Использование функции ВПР

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

Он вернул TRUE для 9-й строки, но искомое значение было Chips, и было найдено совпадение для Chip.

Формула выглядит следующим образом:

=ЕСЛИОШИБКА(ЕСЛИ(ВПР($C$6,$B9,1,ИСТИНА)="Чипы", B9, B9),"Не найдено")

=> ЕСЛИОШИБКА(IF("Chip")="Chips", B9, B9),"Not Found")

[Функция ВПР искала чипы критерия в ячейке диапазона B9 и возвращала значение ячейки, которое равно чипу.]

[Здесь функция ЕСЛИ всегда возвращает значение ячейки, независимо от того, ИСТИНА оно или ЛОЖЬ. В данном случае он вернул Чип.]

[Поскольку первый аргумент функции ЕСЛИОШИБКА не является ошибкой, он возвращает значение, которое является желаемым результатом.]

Вы изменяете выходные данные, изменяя аргументы функций ЕСЛИ и ОШИБКА в формуле.

Заключение

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

Дополнительная литература

Чинмой Мондол

Приветствую! Спасибо, что посетили мой профиль. Я Чинмой Мондоль. Я добросовестный человек, увлекающийся технологиями, с ненасытным аппетитом к знаниям и желанием узнать больше.Я окончил Американский международный университет в Бангладеш со степенью бакалавра в области компьютерных наук и инженерии. Мне нравится использовать свои навыки, чтобы внести свой вклад в захватывающие технологические достижения, которые происходят каждый день. Моими руководящими принципами являются постоянное продвижение и личное развитие.

Чтобы установить значение ячейки, вам нужно использовать свойство «Значение», а затем вам нужно определить значение, которое вы хотите установить. Здесь я использовал несколько примеров, чтобы помочь вам понять это.

1. Введите значение в ячейку

Допустим, вам нужно ввести значение «Готово» в ячейку A1. В этом случае код будет выглядеть примерно так:

Как видите, я сначала определил адрес ячейки, куда я хочу добавить значение, а затем свойство значения. В итоге я присвоил значение «Готово» с помощью знака равенства «=», заключенного в двойные кавычки.

Вы также можете использовать свойство "Ячейки", как в следующем коде.

Приведенный выше код также относится к ячейке A1.

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

Но рекомендуется использовать свойство value для ввода значения в ячейку.

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

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

И если вы хотите ввести значение в активную ячейку, тогда код, который вам нужен, будет выглядеть так:

2. Использование поля ввода

Если вы хотите, чтобы пользователь указал значение для ввода в ячейку, вы можете использовать поле ввода. Допустим, вы хотите ввести значение в ячейку A1, код будет выглядеть так:

В приведенном выше коде значение из ячейки A1 присваивается значению, возвращаемому полем ввода, которое возвращает значение, введенное пользователем.

3. Из другой ячейки

Вы также можете установить значение ячейки, используя значение из другой ячейки. Допустим, если вы хотите добавить значение в ячейку A1 из ячейки B1, код будет таким:

Вы также можете ссылаться на ячейку B1 без использования свойства value.

4. Установить значение во всем диапазоне

Представьте, что вы хотите ввести значения в несколько ячеек или диапазон ячеек вместо одной ячейки, в этом случае вам нужно написать код, как показано ниже:

В первой строке кода у вас есть весь диапазон от ячейки A1 до A10, а во второй строке есть две ячейки B1 и B10.

Получить значение ячейки

Как я уже сказал, вы можете использовать одно и то же свойство value для получения значения из ячейки.

1. Получить значение из ActiveCell

Допустим, вы хотите получить значение из активной ячейки, в этом случае вам нужно использовать следующий код.

В приведенном выше коде вы использовали свойство value с активной ячейкой, а затем присвоили это значение ячейке A1.

2. Назначить переменной

Вы также можете получить значение из ячейки и присвоить его переменной.

Теперь в приведенном выше коде у вас есть переменная «i», которая имеет дату в качестве типа данных. Во второй строке кода переменной присваивается значение из ячейки A1.

3. Показать в MsgBox

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

В приведенном выше коде окно сообщения будет принимать значение из ячейки A1 и показывать его пользователю.

Изменить значение ячейки

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

1. Добавить номер к существующему номеру

Допустим, если вы хотите добавить единицу к числу, которое у вас есть в ячейке A1, вы можете использовать следующий код.

Приведенный выше код присваивает значение ячейке A1, беря значение из самой ячейки A1 и добавляя к нему единицу. Но вы также можете использовать VBA IF THEN ELSE, чтобы написать условие для изменения только тогда, когда в ячейке есть число.

2. Удалить первый символ из ячейки

Теперь следующий код удаляет первый символ из значения ячейки и присваивает остальную часть значения обратно ячейке.

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

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

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

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

Ссылка на ячейку на текущем листе

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

Нажмите на ячейку, в которую вы хотите ввести ссылку на другую ячейку.
Введите в ячейку знак равенства (=).
Щелкните ячейку на том же листе, на который вы хотите создать ссылку, и имя ячейки будет автоматически введено после знака равенства. Нажмите Enter, чтобы создать ссылку на ячейку.

Например, мы щелкаем ячейку B3, в результате чего ячейка, содержащая ссылку, отображает "=B3" и отражает любые изменения данных, сделанные в B3.

Ссылка на ячейку из другого листа в текущей книге

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

Нажмите на ячейку, в которую вы хотите ввести ссылку на другую ячейку.
Введите в ячейку знак равенства (=).
Нажмите вкладку рабочего листа в нижней части окна программы Excel, где находится ячейка, на которую вы хотите сослаться. Строка формул автоматически вводит имя рабочего листа после знака равенства. Восклицательный знак также добавляется в конец имени рабочего листа в строке формул.
Щелкните ячейку, на значение которой вы хотите сослаться, и строка формул автоматически отобразит имя ячейки после имени рабочего листа и восклицательного знака. Нажмите Enter, чтобы создать ссылку на ячейку.

Например, у нас есть электронная таблица, содержащая два рабочих листа с названиями "Данные" и "Расчеты". На листе Расчеты мы хотим сослаться на ячейку из листа Данные. Мы щелкаем вкладку «Рабочий лист данных», затем щелкаем ячейку B3, в результате чего в строке формул отображается «= Data! B3» для ячейки, содержащей ссылку. Данные, отображаемые на листе Расчеты, отражают данные в ячейке B3 на листе данных и изменяются при изменении ячейки B3.

Добавить две ячейки

Вы можете выполнять математические операции с несколькими ячейками, ссылаясь на них в формуле. Например, давайте добавим две ячейки вместе, используя оператор + (сложение) в формуле.

В новом листе введите два значения в ячейки A1 и A2. В этом примере мы введем значение 5 в ячейку A1 и 6 в ячейку A2.
Нажмите ячейку C1, чтобы выбрать ее.В этой ячейке находится наша формула.
Нажмите внутри строки формул и введите =, чтобы начать писать формулу.

Нажмите на ячейку A1, чтобы автоматически вставить ссылку на ячейку в формулу.

Нажмите ячейку B1, чтобы автоматически вставить ссылку на ячейку в формулу.

Нажмите Enter . Ячейка C1, содержащая вашу формулу, автоматически обновит свое значение суммой 5 и 6.

Теперь, если вы измените значения в ячейках A1 или B1, значение в ячейке C1 обновится автоматически.

Не нужно щелкать ячейки, чтобы вставить ссылку на ячейку в формулу. При желании, выбрав ячейку C1, введите =A1+B1 в строке формул и нажмите клавишу ВВОД .

Добавить диапазон ячеек

Вы можете сослаться на диапазон ячеек в формуле, вставив двоеточие (:) между двумя ссылками на ячейки.

Например, вы можете добавить диапазон значений с помощью функции СУММ(). В этом примере показано, как можно суммировать всю строку или столбец значений, указав диапазон между двумя ссылками на ячейки.

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