Две формулы в ячейке в excel
Две формулы в ячейке в excel
Как по мне, то формула в столбцах I и J проще, чем два других варианта. Если в столбце К нет данных, то в I и J тоже пусто и это хорошо, но есть большое но. Если в ячейку К9 поставить ноль (остатка нет), то в ячейке I9 ничего нет, а должна быть сумма остатка на начало и прихода. А условное форматирование и формат ячейки не так работают. Например, ввожу число в ячейку К9, а потом удаляю его. Сумма в ячейке J9 пропадает, а вот количество в ячейке I9 ровно B9+F9.
з.ы. по поводу формулы для расчета количества =ЕСЛИ(K9;B9+F9-K9;””).
Не могу понять логику
Если в ячейке К9 нет данных, то действие B9+F9-K9 не выполняется и в ячейке I9 мы ничего не увидим (“” – ноль)?
И эта формула =ЕСЛИ(K9;I9*D9;””) вообще мне непонятна.
з.ы.2 В идеале вопрос такой:
Есть такие данные:
ячейка А1 – остаток на начало периода;
ячейка В1 – приход товара;
ячейка С1 – продано товара;
ячейка Д1 – остаток на конец периода.
Формула имеет вид: С1= А1+В1-Д1, что бы узнать количество проданного товара за период.
Есть одна проблема. Если в ячейке Д1 нет данных, то в С1 показывается сумма А1 и В1, что не правильно. В конечном результате хотим получить следующее:
если в ячейке Д1 нет данных, то в С1 их тоже не должно быть;
если в ячейке Д1 ноль, то в С1 мы видим сумму А1 и В1.
Как-то так. Спасибо.
Как по мне, то формула в столбцах I и J проще, чем два других варианта. Если в столбце К нет данных, то в I и J тоже пусто и это хорошо, но есть большое но. Если в ячейку К9 поставить ноль (остатка нет), то в ячейке I9 ничего нет, а должна быть сумма остатка на начало и прихода. А условное форматирование и формат ячейки не так работают. Например, ввожу число в ячейку К9, а потом удаляю его. Сумма в ячейке J9 пропадает, а вот количество в ячейке I9 ровно B9+F9.
з.ы. по поводу формулы для расчета количества =ЕСЛИ(K9;B9+F9-K9;””).
Не могу понять логику
Если в ячейке К9 нет данных, то действие B9+F9-K9 не выполняется и в ячейке I9 мы ничего не увидим (“” – ноль)?
И эта формула =ЕСЛИ(K9;I9*D9;””) вообще мне непонятна.
з.ы.2 В идеале вопрос такой:
Есть такие данные:
ячейка А1 – остаток на начало периода;
ячейка В1 – приход товара;
ячейка С1 – продано товара;
ячейка Д1 – остаток на конец периода.
Формула имеет вид: С1= А1+В1-Д1, что бы узнать количество проданного товара за период.
Есть одна проблема. Если в ячейке Д1 нет данных, то в С1 показывается сумма А1 и В1, что не правильно. В конечном результате хотим получить следующее:
если в ячейке Д1 нет данных, то в С1 их тоже не должно быть;
если в ячейке Д1 ноль, то в С1 мы видим сумму А1 и В1.
Как-то так. Спасибо. doctordremlen
Сообщение Как по мне, то формула в столбцах I и J проще, чем два других варианта. Если в столбце К нет данных, то в I и J тоже пусто и это хорошо, но есть большое но. Если в ячейку К9 поставить ноль (остатка нет), то в ячейке I9 ничего нет, а должна быть сумма остатка на начало и прихода. А условное форматирование и формат ячейки не так работают. Например, ввожу число в ячейку К9, а потом удаляю его. Сумма в ячейке J9 пропадает, а вот количество в ячейке I9 ровно B9+F9.
з.ы. по поводу формулы для расчета количества =ЕСЛИ(K9;B9+F9-K9;””).
Не могу понять логику
Если в ячейке К9 нет данных, то действие B9+F9-K9 не выполняется и в ячейке I9 мы ничего не увидим (“” – ноль)?
И эта формула =ЕСЛИ(K9;I9*D9;””) вообще мне непонятна.
з.ы.2 В идеале вопрос такой:
Есть такие данные:
ячейка А1 – остаток на начало периода;
ячейка В1 – приход товара;
ячейка С1 – продано товара;
ячейка Д1 – остаток на конец периода.
Формула имеет вид: С1= А1+В1-Д1, что бы узнать количество проданного товара за период.
Есть одна проблема. Если в ячейке Д1 нет данных, то в С1 показывается сумма А1 и В1, что не правильно. В конечном результате хотим получить следующее:
если в ячейке Д1 нет данных, то в С1 их тоже не должно быть;
если в ячейке Д1 ноль, то в С1 мы видим сумму А1 и В1.
Как-то так. Спасибо. Автор – doctordremlen
Дата добавления – 07.05.2013 в 19:24
Источник: www.excelworld.ru
Объединение двух формул в одну ячейку
Объединение двух таблиц в одну
Помогите, пожалуйста, объединить две таблицы в одну. На одном листе имеется таблица за 2012 год, на.
Перенос формул в ячейки в ячейку с шагом
Доброго дня. Помогите в решении задачи пожалуйста. Необходимо перенести диапазон ячеек содержащий.
Объединение данных в ячейку и вставка на другую страницу
ребята помогите пожта. уже неделю не могу справиться с простой задачкой, наверно в силу.
Импорт из текста, всё в одну ячейку
Здравствуйте уважаемые форумчанины. Подскажите, как можно при получение данных из текстового.
Решение
13.11.2016, 19:44 | |||||||||||||||||||||||||||||||||||||||||||||||||
13.11.2016, 19:44 | |||||||||||||||||||||||||||||||||||||||||||||||||
Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь. Перенос данных столбца в одну ячейку Группировать числа из столбца в одну ячейку Выставить размер страницы в одну ячейку Источник: www.cyberforum.ru Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условийЛогическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия. Функция ЕСЛИ в ExcelФункция имеет следующий синтаксис. ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]) лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару. Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи. Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия. В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо. Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации. Формула ЕСЛИ в Excel – примеры нескольких условийДовольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример. Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид. Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение. Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.
10 формул Excel, которые пригодятся каждомуНе нужно тратить время на подсчёты в Microsoft Excel вручную, ведь существует множество формул, которые помогут быстро справиться с поставленными задачами и повысить точность ваших отчётов. Мы собрали 10 наиболее полезных формул, которые вы сможете выучить за один день. Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5) Функция СУММ позволяет вычислить сумму двух или более чисел. В этой формуле вы также можете использовать ссылки на ячейки. С помощью формулы вы можете:
Англоязычный вариант: =COUNT(A1:A10) Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30). СЧЁТЗАнглоязычный вариант: =COUNTA(A1:A10) С помощью данной формулы можно подсчитать количество заполненных ячеек в одном ряду, то есть тех, в которых есть не только числа, но и другие знаки. Преимущество формулы – её можно использовать для работы с любым типом данных. ДЛСТРАнглоязычный вариант: =LEN(A1) Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак. СЖПРОБЕЛЫАнглоязычный вариант: =TRIM(A1) Данная функция помогает избавиться от пробелов, не включая при этом пробелы между словами. Эта опция может быть чрезвычайно полезной, особенно в тех ситуациях, когда вы вносите в таблицу данные из другого источника и при вставке появляются лишние пробелы. Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без. ЛЕВСИМВ, ПСТР и ПРАВСИМВ=ЛЕВСИМВ(адрес_ячейки; количество знаков) =ПРАВСИМВ(адрес_ячейки; количество знаков) =ПСТР(адрес_ячейки; начальное число; число знаков) Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков). Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции. Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я». Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel». Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel». Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения) Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения) Функция ВПР работает как телефонная книга, где по фрагменту известных данных – имени, вы находите неизвестные сведения – номер телефона. В формуле необходимо задать искомое значение, которое формула должна найти в столбце таблицы. Например, у вас есть два списка: первый с паспортными данными сотрудников и их доходами от продаж за последний квартал, а второй – с их паспортными данными и именами. Вы хотите сопоставить имена с доходами от продаж, но, делая это вручную, можно легко ошибиться.
Эта формула не такая простая, как предыдущие, тем не менее она очень полезна в работе. Формула: =ЕСЛИ(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”) Англоязычный вариант: =IF(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”) Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё. Снова обратимся к примеру из сферы продаж: допустим, что у каждого продавца есть установленная норма по продажам. Вы использовали формулу ВПР, чтобы поместить доход рядом с именем. Теперь вы можете использовать оператор «ЕСЛИ», который будет выражать следующее: «ЕСЛИ продавец выполнил норму, вывести выражение «Норма выполнена», если нет, то «Норма не выполнена». В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1: =ЕСЛИ(B1>C1; “Норма выполнена”; “Норма не выполнена”) Функция «ЕСЛИ» покажет нам, выполнил ли первый продавец свою норму или нет. После можно скопировать и вставить эту формулу для всех продавцов в списке, значение автоматически изменится для каждого работника. СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИФормула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие) =СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения) Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения) Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие. Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине. Используя функции на предыдущем примере, мы можем узнать: СУММЕСЛИ – общий доход только для продавцов, выполнивших норму. СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму. СЧЁТЕСЛИ – количество продавцов, выполнивших норму. КонкатенацияФормула: =(ячейка1&” “&ячейка2) За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&” “&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между ” “, чтобы между объединёнными данными появился пробел. Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;” “; B1) или в англоязычном варианте =concatenate(A1;” “; B1). Кстати, все перечисленные формулы можно применять и в Google‑таблицах. Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel. Источник: blog.teachmeplease.ru Работа в Excel с формулами и таблицами для чайниковФормула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе. Конструкция формулы включает в себя: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки содержащие аргументы и другие формулы. На примере разберем практическое применение формул для начинающих пользователей. Формулы в Excel для чайниковЧтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений. В Excel применяются стандартные математические операторы:
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет. Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат. Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула. При изменении значений в ячейках формула автоматически пересчитывает результат. Ссылки можно комбинировать в рамках одной формулы с простыми числами. Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке. В нашем примере:
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности: Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках. Как в формуле Excel обозначить постоянную ячейкуРазличают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными. Все ссылки на ячейки программа считает относительными, если пользователем не задано другое условие. С помощью относительных ссылок можно размножить одну и ту же формулу на несколько строк или столбцов.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу. Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами. Ссылки в ячейке соотнесены со строкой. Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной). Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.
Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:
При создании формул используются следующие форматы абсолютных ссылок:
Как составить таблицу в Excel с формуламиЧтобы сэкономить время при введении однотипных формул в ячейки таблицы, применяются маркеры автозаполнения. Если нужно закрепить ссылку, делаем ее абсолютной. Для изменения значений при копировании относительной ссылки. Простейшие формулы заполнения таблиц в Excel:
Чтобы проверить правильность вставленной формулы, дважды щелкните по ячейке с результатом. Источник: exceltable.com Microsoft Excelтрюки • приёмы • решения Как в таблицах Excel создавать мегаформулыВ этой статье описан метод объединения нескольких промежуточных формул для создания одной длинной формулы (мегаформулы). Если вы уже поработали в Excel определенное время, то могли видеть длинные формулы, которые были практически непонятны. Здесь вы узнаете, как они были созданы. Поставим цель создать одну формулу, которая удаляет отчество (например, Иван Павлович Петров становится Иваном Петровым). На рис. 125.1 показан лист с некоторыми именами и шесть столбцов промежуточных формул, которые выполняют задачу. Обратите внимание, что формулы несовершенны: они не могут обрабатывать имя из одного слова. Рис. 125.1. Удаление средних имен и инициалов требует указания шести промежуточных формул или одной мегаформулы Формулы, введенные в строке 2, приведены в таблице ниже.
Затратив немного времени, вы можете исключить все промежуточные формулы и заменить их одной мегаформулой. Это можно сделать путем создания всех промежуточных формул и последующего редактирования окончательной формулы (в данном случае формулы в столбце Н) с заменой каждой ссылки на ячейку копией формулы в этой ячейке. К счастью, вы можете использовать буфер обмена для копирования и вставки формул, а не вводить их заново. Продолжайте этот процесс, пока ячейка H1 не станет содержать ничего, кроме ссылки на ячейку А1. В конечном итоге у вас получится следующая мегаформула в одной ячейке: =ЛЕВСИМВ(СЖПРОБЕЛЫ(A2);НАЙТИ(” “;СЖПРОБЕЛЫ(A2);1))&ПРАВСИМВ(СЖПРОБЕЛЫ(A2);ДЛСТР(СЖПРОБЕЛЫ(A2))-ЕСЛИОШИБКА(НАЙТИ(” “;СЖПРОБЕЛЫ(A2);НАЙТИ(” “;СЖПРОБЕЛЫ(A2);1)+1);НАЙТИ(” “;СЖПРОБЕЛЫ(A2);1))) Когда вы будете удовлетворены тем, как работает мегаформула, вы можете удалить столбцы, содержащие промежуточные формулы, потому что они больше не используются. Если вам все еще не ясен описанный процесс, выполните его пошагово.
=ЛЕВСИМВ(B2;C2)&ПРАВСИМВ(B2;ДЛСТР(B2)-ПРАВСИМВ(НАЙТИ(” “;B2;B2+1);C2)) =ЛЕВСИМВ(B2;НАЙТИ(” “;B2;1))&ПРАВСИМВ(B2;ДЛСТР(B2)-ЕСЛИОШИБКА(НАЙТИ(” “;B2;НАЙТИ(” “;B2;1);НАЙТИ(” “;B2;1))) =ЛЕВСИМВ(СЖПРОБЕЛЫ(A2);НАЙТИ(” “;СЖПРОБЕЛЫ(A2);1))&ПРАВСИМВ(СЖПРОБЕЛЫ(A2);ДЛСТР(СЖПРОБЕЛЫ(A2))-ЕСЛИОШИБКА(НАЙТИ(” “;СЖПРОБЕЛЫ(A2);НАЙТИ(” “;СЖПРОБЕЛЫ(A2);1)+1);НАЙТИ(” “;СЖПРОБЕЛЫ(A2);1))) Обратите внимание, что формула в ячейке Н2 теперь содержит ссылки только на ячейку А2. Мегаформула завершена и выполняет те же самые задачи, что и промежуточные формулы (которые теперь можно удалить). Вы можете, конечно, адаптировать этот метод для собственных нужд. Приятной особенностью является то, что одна мегаформула часто рассчитывается быстрее, чем несколько промежуточных формул. При замене ссылок на ячейки текстом формулы убедитесь, что формула продолжает отображать правильный результат после каждой замены. В некоторых ситуациях вам, возможно, понадобится поставить вокруг скопированной формулы круглые скобки. Источник: excelexpert.ru detector |