Форум » КОМПЬЮТЕР » EXCEL- ГОТОВЫЕ И ПОЛЕЗНЫЕ ФОРМУЛЫ - ПОДБОРКА РЕШЕНИЙ » Ответить

EXCEL- ГОТОВЫЕ И ПОЛЕЗНЫЕ ФОРМУЛЫ - ПОДБОРКА РЕШЕНИЙ

Тренер: ФУТБОЛ ДЛЯ ДЕТЕЙ: <<< от 5 до 8 лет >>> И <<< от 9 до 11 ЛЕТ >>> День недели по дате В Интернете на разных сайтах чаще всего встречается такой вариант этой формулы: =ЕСЛИ(ДЕНЬНЕД(B3;2)=1;"Понедельник";ЕСЛИ(ДЕНЬНЕД(B3;2)=2;"Вторник";ЕСЛИ(ДЕНЬНЕД(B3;2)=3;"Среда";ЕСЛИ(ДЕНЬНЕД(B3;2)=4;"Четверг";ЕСЛИ(ДЕНЬНЕД(B3;2)=5;"Пятница";ЕСЛИ(ДЕНЬНЕД(B3;2)=6;"Суббота";"Воскресенье")))))) Очевидно, что в ячейке B3 должна находиться дата, для которой определяется день недели! Однако, этот вариант не оптимален, лучше и удобнее использовать эту формулу: =ВЫБОР(ДЕНЬНЕД(B3;2);"Понедельник";"Вторник";"Среда";"Четверг";"Пятница";"Суббота";"Воскресенье") Другие варианты этой же формулы: =ВЫБОР(ДЕНЬНЕД(B3;2);"ПН";"ВТ";"СР";"ЧТ";"ПТ";"СБ";"ВС") =ВЫБОР(ДЕНЬНЕД(B3;2);"Пн";"Вт";"Ср";"Чт";"Пт";"Сб";"Вс") Проверка ячеек на наличие в них информации Часто бывает так, что необходимо проверить заполнена ли хотя бы одна из ячеек, и, в случае, если не заполнена ни одна, сделать так, чтобы в отдельной ячейке появлялось предупреждающее сообщение. Предложенная ниже формула это лишь один из возможных вариантов реализации такой проверки с помощью сочетания различных функций Excel. =ЕСЛИ(ИЛИ(ДЛСТР(A1)>0;ДЛСТР(A2)>0;ДЛСТР(A3)>0);"";"Не заполнена ни одна ячейка!") Очевидно, что в этой формуле проверяются на выполнение условий "не пусто" ячейки A1, A2, A3! Суммирование нарастающим итогом Допустим, у Вас есть столбец, в котором находятся данные характеризующие продажи за каждый день (неделю, месяц, год:) и Вам необходимо, чтобы рядом находился столбец, в котором бы рассчитывались продажи нарастающим итогом с начала периода. Вам будет полезна следующая формула: =СУММ($B$2:B2) Формула суммирует значения в столбце "B" начиная с ячейки B2. При "растягивании" (автозаполнении) вниз формула будет подсчитывать продажи начиная с первого периода. Формирование списка "Фамилия, инициалы" на основании списка "Фамилия Имя Отчество" Уверен, что у многих, кто работает со списками, в которых указывается фамилия, имя и отчество (работников, сотрудников, знакомых, друзей) возникала необходимость из Иванова Ивана Ивановича сделать "Иванова И.И." Для этого можно использовать Excel с его почти безграничными возможностями! Формула, приведенная в этом выпуске рассылки, поможет Вам для решения этой задачи. Например, в ячейках с A1 по A10 (интервалы ячеек для краткости записываются A1:A10) находится список: Ельцин Борис Николаевич Гурченко Людмила Марковна Горбачев Михаил Сергеевич Кравчук Леонид Макарович Пугачева Алла Борисовна Путин Владимир Владимирович Пушкин Александр Сергеевич Ротару София Михайловна Ульянов Владимир Ильич Шаляпин Федор Иванович Для того, чтобы он стал таким: Ельцин Б.Н. Гурченко Л.М. Горбачев М.С. Кравчук Л.М. Пугачева А.Б. Путин В.В. Пушкин А.С. Ротару С.М. Ульянов В.И. Шаляпин Ф.И. Введите, например, в ячейку B1 формулу =СЦЕПИТЬ(ЛЕВСИМВ(СЖПРОБЕЛЫ(A1);НАЙТИ(" ";СЖПРОБЕЛЫ(A1);1));ПСТР(СЖПРОБЕЛЫ(A1);НАЙТИ(" ";СЖПРОБЕЛЫ(A1);1)+1;1);".";ПСТР(СЖПРОБЕЛЫ(A1);НАЙТИ(" ";СЖПРОБЕЛЫ(A1);НАЙТИ(" ";СЖПРОБЕЛЫ(A1);1)+1)+1;1);".") "Растяните" ее на весь список вниз и Вы получите то, что нужно! Примечание: эта формула будет корректно работать даже в том случае, если есть пробелы перед фамилией и если количество пробелов между словами больше одного Суммирование значений по строкам удовлетворяющим заданному условию Допустим, у Вас есть список в 2-х столбцах. В первом столбце название, во втором числовое значение (сумма). Пример. В диапазоне A2:A6 - названия, в диапазоне B2:B6 - суммы (знак "|" в нашем примере служит для символического представления столбцов): Закупка товара | 110 Закупка канцелярии | 12 Закупка товара | 202 Закупка сырья | 468 Закупка товара | 245 Как одной формулой, посчитать на какую сумму закупили товар? То есть, как одной формулой прийти к значению 557 (общая сумма, на которую закупили товар)? Решение (формула): =СУММЕСЛИ(A2:A6;"Закупка товара";B2:B6) Функция СУММЕСЛИ может успешно применяться для решения аналогичных задач. Синтаксис функции: СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования]. Параметр "диапазон_суммирования" не является обязательным. ПРИМЕР: СУММЕСЛИ(B2:B6;">100") - подсчитывает сумму всех значений больше 100. Примечание: эта формула будет подсчитывать значения только напротив строк содержащих текст "Закупка товара". Если же текст будет отличаться хотя бы на 1 символ, формула работать не будет. То есть, если бы в нашем примере была строка "Закупка товаров" или "Закупка товар", то значения стоящие напротив этих строк не вошли бы в общую сумму. Как подсчитать значения для строк содержащих заданное словосочетание, мы напишем в одном из следующих выпусков рассылки. Определение сегодняшней даты Эта формула будет полезна тем, кому необходимо, чтобы постоянно в определенной ячейке выводилась сегодняшняя дата. Как и для большинства простых задач есть несколько вариантов решений: 1 Вариант =СЦЕПИТЬ("Сегодня ";ТЕКСТ(C1;"Д ММММ, ГГГГГ");" года") 2 Вариант ="Сегодня "&ТЕКСТ(C1;"Д ММММ, ГГГГГ")&" года" Как Вы видите, обе эти формулы возвращают аналогичный результат. Разница только в том, что в первом случае использована функция "СЦЕПИТЬ", а во втором использован оператор &. Как сказано в справке Excel "Текстовый оператор конкатенации" (амперсанд), который "используется для объединения нескольких текстовых строк в одну строку". Однако, можно, конечно же, втавить эту функцию в рассмотренные формулы, результат останется прежним, а формулы, соответственно, преобразятся и примут следующий вид: =СЦЕПИТЬ("Сегодня ";ТЕКСТ(СЕГОДНЯ();"Д ММММ, ГГГГГ");" года") ="Сегодня "&ТЕКСТ(СЕГОДНЯ();"Д ММММ, ГГГГГ")&" года" Рассчет среднего значения Средние значения очень часто используются для анализа различных данных. Существует встроенная функция, которая рассчитывает среднее значение, однако из-за определенных особенностей Вашей информации (наличия информации), она не всегда может корректно работать. Стандартная (штатная) функция Excel для рассчета среднего значения (например, в диапазоне A1:A8): =СРЗНАЧ(A1:A8) Ее аналог с использованием двух функций, =СУММ() и =СЧЁТ(): =СУММ(A1:A8)/СЧЁТ(A1:A8) Примечание: как известно "среднее значение" по определению рассчитывается как общая сумма на количество. А что делать, если некоторые данные в анализируемом диапазоне не заполненны, либо стоит 0 там, где его не может быть, либо вместо числа стоит текст? Ведь в этом случае формулы =СРЗНАЧ(A1:A8) и/или =СУММ(A1:A8)/СЧЁТ(A1:A8) могут возвратить некорректный результат. Ниже рассмотрен вариант, который учитывает некоторые нюансы. =СУММЕСЛИ(A1:A8;">0")/СЧЁТЕСЛИ(A1:A8;">0") Эта формула, рассчитывает среднее значение только для положительных чисел входящих в указанный диапазон (формула будет полезна в том случае, если у Вас данные для которых рассчитывается среднее, никогда не могут быть отрицательными или равными нулю, а если они =0 или отрицательные, то это означает, что их нет или данные с ошибкой). Таким образом, даже не смотря на то, что в указанном диапазоне могут присутствовать ошибочные данные Вы будете получать среднее значение только для корректных данных. ПРОВЕРКА ДАННЫХ. Проверка введенных данных очень актуальный вопрос. Сочетание рассмотренных формул и функции ЕСЛИ, это еще один способ проверить были ли верны все данные в указанном диапазоне. Вариант формулы: =ЕСЛИ(СРЗНАЧ(A1:A8)-СУММЕСЛИ(A1:A8;">0")/СЧЁТЕСЛИ(A1:A8;">0")=0;"Все правильно";"Ошибка") Проверка наличия и корректности данных и расчет значения Допустим, у Вас есть таблица, в которой 3 столбца: "Фамилия И.О.", "Продажи за 1-й месяц" и "Продажи за 2-й месяц" (столбцы A, B, C соответственно) и Вы хотите рассчитать во сколько раз тот или иной торговый агент продал больше или меньше во 2-м месяце по отношению к первому. Конечно, рассчитывается это довольно просто: =C2/B2. Однако, может быть так, что кто-то из торговых агентов не работал в прошлом месяце (1 случай), либо в первом работал, а во втором нет, то есть, введя формулу (=C2/B2) и "растянув" ее до конца списка Вы увидите либо #ДЕЛ/0! (1-й случай), либо 0 (второй случай). Чтобы избежать этого воспользуйтесь формулой: =ЕСЛИ(ТИП(C2/B2)=16;"";C2/B2) Эту формулу можно модифицировать. Например, Вы хотите, чтобы напротив тех, кто работал во втором месяце и не работал в первом, стояла 1 (единица), а напротив тех, кто работал в первом и не работал во втором месяце стоял 0 (ноль). Формула примет такой вид: =ЕСЛИ(И(ТИП(C2/B2)=16;B2=0);1;C2/B2) Примечание: следует учесть, что если во втором и третьем столбце будет введен текст вместо числа, то первая из приведенных формул сработает более корректно, а вторая возвратит значение ошибки #ЗНАЧ! (однако, и эту проблему можно решить, но для этого нужно еще более усложнить формулу - попробуйте сделать это сами:) Получение данных другого листа (в Excel) с помощью формулы =ДВССЫЛ(АДРЕС(A1;B1;;;C1)) Эта формула позволяет получать данные (значения) на текущем листе из любого другого листа книги изменяя по своему усмотрению номер строки, номер столбца и/или имя листа. Для этого достаточно ввести в ячейку A1 - номер строки, в ячейку B1 - номер столбца, в ячейку C1 - имя листа. Данная формула приводится для того, чтобы обратить Ваше внимание на функции относящиеся к кактегории "Ссылки и массивы". Использование этих функций дает существенные дополнительные возможности при работе с данными. Комбинации клавиш Excel бывают разными Сочетание клавишь Excel - применение автофильтра. Excel предоставляет очень хорошие возможности отбора данных с помощью, так называемого "Автофильтра". Как Вы, вероятно, знаете, чтобы "включить" автофильтр необходимо: 1. Стать в верхнюю строку диапазона данных, к которому будет применен автофильтр. 2. В строке меню выбрать Данные - Фильтр - Автофильтр. После этого в левой части каждой ячейки, которая воспринята как заголовок столбца диапазона, появляются стрелочки, позволяющие отбирать данные по заданным критериям. Однако, это же действие (включение автофильтра) можно сделать с помощью клавиатуры. Для этого последовательно нажмите Alt, затем "д", затем "ф", затем "а". Совет по использованию Excel Вероятно, Вам уже не раз приходилось строить диаграммы и графики в Excel. С их помощью можно очень наглядно продемонстрировать изменение тех или иных показателей. Но есть одно "но", о котором, скорее всего, далеко не все знают. Речь идет о том, что стандартно (то есть в настройках "по умолчанию") в диаграмме отображаются только те данные, которые видимы на листе. Если же вручную или с помощью автофильтра скрыть строки, то диаграмма преобразиться - исчезнут некоторые значения. Чтобы этого не случилось следует: 1) выделить диаграмму; 2) "Сервис" - "Параметры" - вкладка "Диаграмма" и убрать отметку напротив строки "Отображать только видимые ячейки". Определение числа =КОНМЕСЯЦА(B4;0)-ДАТА(A1;ПОИСКПОЗ(A2;{"Январь";"Февраль";"Март";"Апрель";"Май";"Июнь";"Июль";"Август";"Сентябрь";"Октябрь";"Ноябрь";"Декабрь"};0);1)+1 Сегодня формула приводится, как говорится, "без комментариев". То есть Вам предлагается самим догадаться, что делает (для чего предназначена) эта формула, но Вы можете зайти на форум, чтобы спросить зачем она нужна у автора рассылки, или прочитать догадки других. Однако, необходимо сказать, что эта формула будет "работать" только при активированном "Пакете анализа" (Сервис-Надстройки-Пакет анализа). Почему? - задать вопрос на форуме. Формула может показаться немного длинноватой и/или сложноватой, что, возможно, смутит тех, кто в Excel делает "первые шаги", но не стоит волноваться. Уверен, что со временем, если Вы будете активно работать в Excel, Вы сможете создавать и более сложные формулы. Для справки: в Excel есть ограничения на длину формулы. По нашим сведениям, она не может превышать 1000 символов. Вставка имени листа =ПСТР(ЯЧЕЙКА("filename";A1);НАЙТИ("]";ЯЧЕЙКА("filename";A1))+1;32) Данная формула вставляет (возвращает) имя листа в ту ячейку, в которой находится. Формула может быть полезна, тем, кто пользуется функциями вида =ДВССЫЛ или =АДРЕС. Немаловажно отметить, что эта формула будет "работать" только в сохраненном файле.

Ответов - 48, стр: 1 2 All

Тренер: Особенности суммирования с применением функции СМЕЩ Название: Вставка имени листа =СУММ(СМЕЩ(A2;B1;;C1;)) Применение функции СМЕЩ существенно увеличивает возможности работы с данными в Excel. Приведенная выше формула позволяет суммировать значения из различных диапазонов изменяя параметры диапазона. Примеры: Пример 1 Если в диапазоне A2:A10 находятся числа 3, 3, 5, 5, 4, 7, 6, 7, 9, а значение в ячейках B1=0, C1=2, то мы получим сумму диапазона A2:A3, то есть 6 (шесть). Пример 2 Если в диапазоне A2:A10 находятся числа 3, 3, 5, 5, 4, 7, 6, 7, 9, а значение в ячейках B1=0, C1=5, то мы получим сумму диапазона A2:A6, то есть 20 (двадцать). Пример 3 Если в диапазоне A2:A10 находятся числа 3, 3, 5, 5, 4, 7, 6, 7, 9, а значение в ячейках B1=2, C1=4, то мы получим сумму диапазона A4:A7, то есть 21 (двадцать один). Общий синтаксис функции СМЕЩ: СМЕЩ(ссылка ;смещ_по_строкам;смещ_по_столбцам;высота;ширина) Игнорирование нулевых значений =ЕСЛИ(МИН(A2:A21)=0;НАИМЕНЬШИЙ(A2: A21;2);МИН(A2: A21)) Эта формула возвращает минимальное значение для диапазона A2:A21, при этом, если в диапазоне A2:A21 встречаются 0 (нулевые значения), то определяется минимальное значение без учета нулей. Определение количества заданного символа в ячейке содержащей текст =ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;"")) (базовая формула) Эта формула позволяет определить сколько раз символ, который находится в ячейке B1 повторяется (входит) в ячейку содержащую текст (в нашем случае это ячейка A1). На примере этой формулы покажем, как создаются более сложные формулы, в которых присутствуют встроенные проверки. В нашем случае следует проверить: 1) содержится ли в ячейке A1 текст (а не число или дата, или время и т.д.); 2) введен ли в ячейку B1 только 1 символ (если несколько, то первоначальная формула даст некорретный результат). Последовательно будем добавлять различные усовия/проверки используя функцию ЕСЛИ. Вот реализация первой проверки: =ЕСЛИ(ТИП(A1)=2;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;""));"в ячейке не текст") То есть мы добавили 1 условие, которое реализовано с помощью логической функции ЕСЛИ и функции проверки свойств и значений ТИП. В случае выполнения условия ТИП(A1)=2, означающему, что ячейка A1 - текстовая, мы переходим к расчету (базовая формула), в случае, если условие не выполняется, то появится сообщение "в ячейке не текст". Следующее условие, выполнение которого нам нужно проверить: в ячейке B1 - один символ или больше? Если в ней только один символ - проводим вычисления, если больше - выдаем предупреждающее сообщение. Действуем аналогично, то есть используем еще одну функцию ЕСЛИ, а также воспользуемся функцией ДЛСТР, которая определит сколько символов в нужной нам ячейке. Получаем такую формулу: =ЕСЛИ(ДЛСТР(B1)<>1;"символ не введен, либо количество символов больше одного";ЕСЛИ(ТИП(A1)=2;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;""));"в ячейке не текст")) Как видите, мы просто добавили еще одно условие, а также воспользовались формулой полученной на предыдущем этапе. Проверка на наличие ошибок Описание проблемы: когда определенные ячейки, значения из которых используются в формулах, не заполнены данными (либо в других случаях), появляется сообщение, информирующее об ошибке, например #ДЕЛ/0! (деление на ноль). Решение проблемы: использовать функцию ЕСЛИ() и встроенные функции, которые относятся к категории "Проверка свойств и значений". Пример: формула =F1/G1 Если в ячейке G1 окажется 0 (или ячейка окажется пустая), то результатом будет значение ошибки #ДЕЛ/0! Решение: =ЕСЛИ(ЕОШ(F1/G1);""; F1/G1) Замечания: в случае, если условие выполняется то формула =ЕСЛИ(ЕОШ(F1/G1);""; F1/G1) вернет значение, которое условно можно назвать "пустая ячейка". То есть, если, например, эта формула находится в ячейке E1, а также есть ячейка в которой Вы получаете сумму значений из некоторых ячеек включая E1 используя знак "плюс", то в итоге Вы не получите необходимый Вам результат. Вы не увидите сумму, Вы увидите значение ошибки #ЗНАЧ! Поэтому для суммирования значений рекомендуется пользоваться не знаком плюс =E1+F1, а функцией СУММ(), то есть вместо =E1+F1 следует использовать СУММ(E1:F1). Кроме функции ЕОШ() существуют также и другие функции, которые можно и нужно использовать для проверки свойств и значений. Обратите внимание (!): следует учесть, что если мы не видим значения ошибок, нам сложнее их отыскать, поэтому рекомендуется очень осмотрительно пользоваться подобными формулами - они, как Вы уже поняли, будут приводить к тому, что визуально ячейка будет восприниматься как пустая. Подсчет значений в строках с заданным словом Как одной формулой посчитать общий итог для всех промежуточных строк, содержащих заданное слово, например, слово "итого"? Для примера (чтобы проще было понять, о чем идет речь) ниже приведена часть таблицы. Подразумевается, что напротив каждого завода, фабрики, комбината есть числовые данные, а в строках с "итого" использована функция СУММ. Завод20 Завод40 Завод80 Итого заводы Фабрика111 Фабрика222 Фабрика333 Фабрика777 Итого фабрики Комбинат10001 Комбинат20002 Комбинат30003 Комбинат40004 Комбинат50005 Комбинат70007 Итого комбинаты ИТОГО ВСЕ: /необходимая нам формула/ Обычно, это делают, выбирая каждую ячейку напротив промежуточного "итого" при вводе формулы СУММ или через "плюс": Однако, есть способ и проще и удобнее. Допустим, названия наших заводов, фабрик, комбинатов находятся в столбце A в интервале A2:A17, а в столбцах, начиная со столбца B и далее, находятся данные по годам. Чтобы посчитать сумму значений всех ячеек столбца B напротив которых имеется ячейка (из столбца A) содержащая слово "итого" следует воспользоваться формулой: =СУММЕСЛИ(A2:A17;"*итог*";B2:B17) Однако, эта формул еще "не совершенна": Ведь нам необходимо посчитать итоги не только для первого года (столбец B), но и для других. Если же мы попробуем "растянуть" получившуюся формулу хотя бы на 1 ячейку вправо, мы получим =СУММЕСЛИ(B2:B17;"*итог*";C2:C17), что, конечно же, нас совсем не устроит. Поэтому, первоначальную формулу нужно отредактировать, присвоив диапазону A2:A17 абсолютную адресацию: =СУММЕСЛИ($A$2:$A$17;"*итог*";B2:B17) Теперь эту формулу можно спокойно "растягивать" на необходимое количество столбцов! Просуммировать все значения в строке указав номер строки Как известно все строки пронумерованы от 1 до 65536. Как известно, одна из наиболее востребованных задач, которые решаются в Excel это суммирование значений. Используя нижеприведенную формулу Excel можно просуммировать все значения в строке, номер которой Вы сами укажите в отдельной ячейке. По просьбам читателей рассылки приводим также "английский" вариант формулы, если в нем Вами будут найдены ошибки, просим написать нам об этом на указанный в конце рассылки адрес электронной почты. Обращем внимание, что "английская" формула не будет работать в руссифицированном Excel и наоборот. =СУММ(ДВССЫЛ(АДРЕС(A1;1;1;;)):ДВССЫЛ(АДРЕС(A1;256;1;;))) =SUM(INDIRECT(ADDRESS(A1;1;1;;)):INDIRECT(ADDRESS(A1;256;1;;))) в ячейке A1 следует указать номер необходимой Вам строки эту формулу следует вводить в любую незанятую ячейку первой строки (естественно, что кроме ячейки A1, которая уже занята) Ограничения: 1) если в ячейку A1 будет введен ноль (или она будет пустая), отрицательное число, текст, то формула возвратит значение ошибки #ЗНАЧ!; 2) если в A1 будет введена единица или логическое значение ИСТИНА (которое в формулах интерпретируется как единица), то Вы получите сообщение о циклической ссылке; 3) если хотя бы в одной ячейке указанной Вами строки будет значение ошибки, то Вы также не получите нужный Вам результат (сумму). Несмотря на ограничения использование этой формулы и формул, построенных на ее основе, может существенно упростить решение определенного типа задач.Попробуйте поэкспериментировать с этой формулой, и Вы сами в этом убедитесь. Формула приведена не как готовое универсальное решение, а как шаблон, на основе которого можно создавать множество различных вариантов формул. Разбить текст в ячейке на 2 части с помощью формулы Excel Часто бывает необходимо разбить текст в ячейке на несколько частей. Это можно довольно просто сделать используя формулу (сочетание функций) в Excel. Допустим, в ячеках B2:B8 находится текст: 200 школьных кроссвордов (1-2 классы)./Сухин И.Г. Диагностика интеллекта методом рисуночного теста./Степанов С.С. Комплексный анализ текста. 5 кл. Рабочая тетрадь./Малюшкин А.Б. Конспекты интегрир. уроков гуманит. цикла 5-7 классы./Малюшкин Логоритмические занятия в детском саду./Картушина М.Ю. Общая психокоррекция: Учебник для вузов./Осипова А.А. Практическая психология в начальной школе./Овчарова Р.В. Необходимо с помощью формул разбить этот текст на 2 столбца в первом из которых будет только название, а во втором автор книги. Это можно сделать с помощью формул Excel, которые Вы найдете ниже. При этом основной идей является отыскание символа-разделителя (в данном случае это символ "/"). =ЛЕВСИМВ(B2;НАЙТИ("/";B2;1)-1) =ПСТР(B2;НАЙТИ("/";B2;1)+1;1000) очевидно, что первая из формул вводится в ячейку C2, а вторая в ячейку D2 Аналогичные формулы можно использовать не только в том случае, если разделителем является какой-либо конкретный символ. ормулы следует отредактирвать в зависимости от особенностей исходного текста. Английский эквивалент формул: =LEFT(B2;FIND("/";B2;1)-1) =MID(B2;FIND("/";B2;1)+1;1000) Проссуммровать данные в зависимости от названия столбцов В Excel нам приходится работать с различными вариантами таблиц. Правильно оформленная таблица всегда имеет заголовки столбцов. Если в заголовке столбцов встречается одно и то же слово, например, "сумма", можно одной формулой просуммировать данные относящиеся к нужным столбцам. Допустим, в строках идут названия различных порошков, а в столбцах по месяцам "штук" и "сумма". Обычно, для того, чтобы получить сумму за все месяцы прибегают к формулам вида: =E4+G4+I4+K4+M4+O4 или =СУММ(E4;G4;I4;K4;M4;O4), при этом, для того, чтобы ввести эти формулы необходимо указать каждую ячейку по отдельности да еще и поставить дополнительный знак (в первом случае это "+", во втором ";"). Не очень удобный способ, если количество столбцов очень большое и приходится делать это часто для разных таблиц. Предлагаемая ниже формула позволяет решить описанную задачу очень простым и эффективным способом: =СУММЕСЛИ($D$3:$O$3;Q$3;$D4:$O4) англ. вариант =SUMIF($D$3:$O$3;Q$3;$D4:$O4)

Тренер: Возможности функции СУММПРОИЗВ() для суммирования по нескольким критериям Допустим, у нас есть таблица, в которой хранятся такие данные: дата, поставщик, товар, сумма. Из практики работы Вы наверняка сталкивались с ситуацией, когда необходимо было просуммировать не только на какую сумму поставлено товара от определенного поставщика, но и какого именно товара. Как известно, функция СУММЕСЛИ() позволяет нам получить сумму по определенному критерию, но только по одному. Для того, чтобы получить сумму сразу по нескольким критериям, она не подходит. Для этих целей предлагаем Вам пользоваться функцией СУММПРОИЗВ(). В приведенной ниже формуле в диапазоне B2:B51 находятся названия поставщиков, в диапазоне C2:C51 – названия товаров, в D2:D51 – сумма поставки. =СУММПРОИЗВ(--(B2:B51=H4);--(C2:C51=H5);D2:D51) англ. вариант =SUMPRODUCT(--(B2:B51=H4);--(C2:C51=H5);D2:D51) Эта формула позволяет узнать, на какую сумму было получено товаров с именем указанным в ячейке H5 от поставщика имя которого указано в ячейке H4. Также функцию СУММПРОИЗВ() можно использовать и в других случаях. Условное форматирование ячейки диапазона, содержащей максимальное значение Иногда бывает необходимо визуально выделить ячейки, содержащие максимальные значения. Допустим, в таблице приведены данные о количестве человек, сделавших покупку в разных магазинах за определенный период. Чтобы с помощью условного форматирования выделить ячейку (или несколько ячеек), содержащую максимальное значение диапазона воспользуйтесь формулой: =B2=МАКС($B2:$K2) англ. вариант =B2=MAX($B2:$K2) Как эта формула была применена для ячеек B2:K2 (последовательность действий): 1. Выделите и скопируйте формулу ("=B2=МАКС($B2:$K2)") в буфер обмена; 2. Станьте в ячейку B2; 3. Зайдите: Формат - Условное форматирование; 4. Выберите в выпадающем списке (стрелочка под словом "Условие 1") "формула"; 5. В поле правее вставьте формулу =B2=МАКС($B2:$K2); 6. Задайте нужный формат и нажмите "Ок"; 7. Стоя в ячейке B2 в контекстном меню (правая кнопка мышки) выберите "Копировать"; 8. Выделите ячейки C2:K2 и в контекстном меню выберите "Специальная вставка" - "Форматы" и нажмите "Ок". После этого ячейка (или ячейки) содержащая максимальное значение отформатируется форматом заданным на шаге 6. Извлечение числа в формате числа из текстовой строки с помощью формулы Excel Часто в текстовых строках содержится "смешанная" информация. Например в одной ячейке храниться текст вида: "Получено от _название_ (руб.): 2460,98". Допустим имеется информация в таком виде по нескольким сотням покупателей, названия которых разные. Если мы захотим быстро получить общую сумму, то нам необходимо предварительно обработать такую информацию отделив цифровые данные от текстовых. Это можно сделать используя принцип, описанный ранее в одной из рассылок, однако, есть существенный момент, заключающийся в том, что число может быть воспринято как текст. Чтобы этого не произошло следует воспользоваться функцией ЗНАЧЕН(). Таким образом, если в ячейке A2 храниться текст "Получено от уважаемого покупателя (руб): 2460,98", то чтобы получить в отдельной ячейке 2460,98 примененим формулу: =ЗНАЧЕН(ПСТР(A2;НАЙТИ(":";A2;1)+ДЛСТР(":");ДЛСТР(A2)-НАЙТИ(":";A2;1))) англ. вариант =VALUE(MID(A2;FIND(":";A2;1)+LEN(":");LEN(A2)-FIND(":";A2;1))) ВАЖНО:в английских версиях Excel в качестве разделителя в формулах используется не точка с запятой, а запятая, поэтому, вероятнее всего, формула будет выглядеть так: =VALUE(MID(A2,FIND(":",A2,1)+LEN(":"),LEN(A2)-FIND(":",A2,1))) Изменение всех букв в ячейке на прописные с помощью формулы Excel Для того, чтобы не перенабирать текстовую информацию "с большой буквы" можно воспользоваться встроенной функцией ПРОПИСН(). Достаточно просто указать ссылку на ячейку и в качестве результата Вы получите всю фразу с большой буквы. Как известно ссылки на ячейки бывают относительные и абсолютные, описание их Вы может почитать во многих пособиях по Excel. Однако, довольно редко пишут еще об одной разновидности ссылок. В приведенной ниже формуле как раз используется такая ссылка. =ПРОПИСН(ДВССЫЛ("R[-1]C";)) англ. вариант =UPPER(INDIRECT("R[-1]C";)) ВАЖНО:в английских версиях Excel в качестве разделителя в формулах используется не точка с запятой, а запятая, поэтому, вероятнее всего, формула будет выглядеть так: =UPPER(INDIRECT("R[-1]C",)) Особенностью такой адресации является то, что значение, которое подставляется в формулу берутся из ячеек находящихся по отношению к ячейке с формулой на определенном расстоянии левее/правее или выше/ниже. То, есть в данном случае "R[-1]C" означает, что следует взять значение из ячейки находящейся в том же столбце, но на строку выше. Примеров использования такого вида ссылок может быть несколько. С одним из них Вы можете ознакомиться скачав файл. Формула, на которую мы бы рекомендовали обратить Ваше внимание, находиться по адресу: Лист "1_по_новому", ячейка O10 и ниже. Часть этой формулы занимается тем, что в случае, если в определенной ячейке слово "ИТОГО", то происходит суммирование начиная с фиксированной ячейки и по ячейку, которая над ячейкой с формулой. Отображение в одной и той же ячейке Excel несколько значений на разных строках Иногда бывает необходимо, чтобы в одной и той же ячейке можно было увидеть сразу и результат продаж за месяц и процент увеличения/снижения по отношению к предыдущему месяцу. Формула Excel позволяет это сделать. Собственно формула: =B2&СИМВОЛ(10)&ТЕКСТ(B4;"+0,0%;-0,0%;") Формулу лучше всего понимать, имея перед глазами пример. Обязательное условие: для ячеек, содержащих формулу должно быть установлено «переносить по словам» (Формат-Ячейки… вкладка «Выравнивание»). Очевидно, что значение возвращаемое такой формулой будет иметь текстовый формат, т.е. с ним невозможно будет осуществлять арифметические действия. Суммирование в Excel с помощью трехмерной ссылки Создатели Excel предусмотрели возможность суммирования сразу на нескольких листах не перечисляя все эти листы. Допустим, есть файл с именами листов (листы расположены в указанном порядке): сводный_лист, Январь, Февраль, Март, Апрель, Май, Июнь, Июль. На листах с названиями месяцев приведена информация о продажах, которая находиться на каждом листе в ячейке C11. Для того, чтобы одной формулой посчитать продажи за все перечисленные месяцы, не заходя на каждый лист, лучше всего воспользоваться формулой (поместив ее, например, на сводный лист): =СУММ(Январь:Июль!C11) Комментарий: такая формула просуммирует все числа находящиеся в ячейке C11 всех листов с "Январь" по "Июль". Если между эти листами будут вставлены еще другие листы, то в сумму попадут значения и вставленных листов. И наоборот, если будут удалены листы, то сумма уменьшиться. Функции, в которых допустимо использовать трехмерные ссылки (по алфавиту): ДИСП, ДИСПА, ДИСПР, ДИСПРА, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СРЗНАЧ, СРЗНАЧА, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, СУММ, СЧЁТ, СЧЁТЗ. Как выбрать значение из таблицы используя стандартные функции Excel Авторы рассылки много времени проводят на различных русскоязычных форумах посвященных Excel. Нами было замечено, что одним из наиболее часто встречающихся вопросов является вопрос о том, как можно выбрать по заданному слову значение из соседнего столбца. Например, есть таблица состоящая из двух столбцов. В первом перечислены имена участники соревнования, а во втором указано количество набранных ими очков. Есть стандартная функция, которая позволяет для заданного имени получить количество очков. Эта функция ВПР(), которая имеет следующий синтаксис: ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр). Рассмотрим немного подробнее каждый из параметров. искомое_значение - то, что функция ВПР() будет искать в 1-м столбце массива под названием "таблица" (см. следующий параметр) таблица - диапазон ячеек в котором находятся данные (в нашем примере B2:C6) номер_столбца - номер столбца из которого необходимо вернуть значение для заданного имени интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.(из справочной системы Excel) итак, формула, которая позволяет получить количество очков для указанного имени (фамилии). =ВПР("Белов";B2:C6;2;ЛОЖЬ) Комментарий: Подразумевается, что в диапазоне B2:C6 находятся данные с фамилиями (1-й столбец) и результатми (2-й столбец диапазона). Вместо "Белов" можно указать ссылку на ячеку содержащую слово "Белов" (или любую другую имеющуюся в списке фамилию). Если же будет задана фамилия, которой нет в списке функция ВПР() вернет значение ошибки #Н/Д. Предостережение: Если в Ваших реальных данных одна и та же фамилия (название товара и т.д. и т.п.) встречается более 1 раза, то функция ВПР() вернет значение только для первого встречающегося значения (фамилии, товара и пр.).

Тренер: Получение с помощью формулы Excel значения средневзвешенной цены Для тех, кому приходиться рассчитывать средневзвешенную цену, приводим формулу, с помощью которой это можно сделать без использования промежуточных столбцов. В диапазоне B3:B9 находится "количество", а в диапазоне C3:C9 - "цена". =СУММ(C3:C9*(B3:B9/СУММ(B3:B9))) ВНИМАНИЕ! формулу следует вводить как формулу массива, то есть одновременым нажатием трех клавиш Ctrl+Shift+Enter Получение значения таблицы из той же строки, но другого столбца без использования функции ВПР() Несмотря на то, что функция ВПР() является довольно удобной для решения некоторых задач, она, все же, не позволяет получить значение из той же строки, но другого столба для искомого значения, если это значение не находиться в крайнем левом столбце диапазона. Например, в столбце правее находятся фамилии, а в столбце левее табельный номер. Необходимо зная фамилию получить табельный номер. С помощью функции ВПР() сделать это невозможно. Для решения подобных задач следует применять формулу, которую Вы найдете ниже. В ней использованы 2 функции: ИНДЕКС() и ПОИСКПОЗ(). =ИНДЕКС(A2:A10;ПОИСКПОЗ(D14;B2:B10;0);1) Фамилии находятся в диапазоне B2:B10, табельные номера в диапазоне A2:A10. Фамилия, для которой мы хотим получить значение табельного номера в ячейке D14. ВНИМАНИЕ! Формула будет возвращать корректный результат только в случае, если в списке не будет повторяющихся фамилий. В случае, если в D14 (искомая фамилия) будет введена фамилия, которая отсутствует в списке, формула вернет значение ошибки #Н/Д.

Тренер: Фото в примечаниях к ячейке 1. Откройте панель инструментов Рисование. Меню Вид - Панели инструментов - Рисование (View - Toolbars - Drawing) 2. Щелкните по ячейке, в которую будем вставлять примечание правой кнопкой мыши и выберите в контекстном меню Добавить примечание (Add comment) 3. Чтобы примечание не скрывалось во время редактирования, щелкните по ячейке правой кнопкой еще раз и выберите Изменить примечание (Edit comment) 4. Щелчком левой кнопки мыши выделите штрихованную рамку вокруг примечания (штриховка должна превратиться в точки). 5. На панели инструментов Рисование разверните палитру Цвет заливки и выберите - Способы заливки, далее вкладка Рисунок. 6. Щелкнув по кнопке Рисунок, выберите файл с изображением и установите флажок Сохранять пропорции рисунка. Чтобы добавить примечание сразу в несколько ячеек: Копируем ячейку с примечанием > Выделяем ячейки куда хотим вставить примечание > Правой кнопкой мыши - Специальная всатвка, выбираем точку "Примечания". Переделал довольно известную формулу Выпадающий список в ячейке с удалением использованных элементов... сам не знаю зачем. Вот мой вариант: http://moitrener.narod.ru/Excel/MoiVariant.xls

okamenzeva: у меня не получаеться((((((

Тренер: okamenzeva пишет: у меня не получаеться(((((( http://sizop.my1.ru/_fr/1/primer080226_SU.zip

okamenzeva: большое человеческое спасибо

yalok: А как создать формулу, для суммирования чисел по цвету?

Тренер: yalok пишет: А как создать формулу, для суммирования чисел по цвету? Скажите, как у вас числа покрасились и я скажу, как их суммировать : )

yalok: ну, например: в столбике цифры 15-крас., 20-син., 25-крас., 30-=син., 35-крас. Мне надо, чтоб красные проссумировались в одной ячейке, а синие - в другой ))))) Как это сделать?

Тренер: Раз вы цифры сами руками покрасили, а не условным форматированием, то и суммируйте их руками. Если есть всёж другая задача, то давайте конкретный файл с расширением xls, я его суммирую вам.

yalok: Хорошо, тогда как эти цифры покрасить "условным форматировнием"?

Тренер: Читайте справку, кнопка F1 на клавиатуте, при открытом Excel.

yalok: Ну помогите мне a b c d 1 фио 10 000,00р. опл 2 фио 12 000,00р. неопл 3 фио 15 000,00р. опл 4 фио 9 000,00р. опл 5 фио 18 000,00р. неопл 6 фио 14 000,00р. опл 7 8 сумма опл 9 сумма неопл Если фио "неопл", то красным цветом; если " опл" - то синим...... а потом проссумировать))))

Тренер: http://moitrener.narod.ru/Excel/UslFormat.xls

yalok: Спасибо БОЛЬШОЕ!!!!!!!!!

Bulat: Тренер Добрый день! Я только начинаю постягать основы Екселя, поэтому был очень рад попасть на фаш форум. Спасибо за его создание! Не могу справиться с формулой. Внутри файла есть исход. данные и таблица с указанием места, куда нужно вставить формулу. Не могли бы вы помочь? С уважением. http://zalil.ru/30322385 e-mail: antonxbulat@yandex.ru

Тренер: Bulat пишет: Тренер Добрый день! Я только начинаю постягать основы Екселя, поэтому был очень рад попасть на фаш форум. Спасибо за его создание! Не могу справиться с формулой. Внутри файла есть исход. данные и таблица с указанием места, куда нужно вставить формулу. Не могли бы вы помочь? С уважением. http://zalil.ru/30322385 e-mail: antonxbulat@yandex.ru Друзья! Я тренер по футболу и программированием занимаюсь в свободное от работы время, которого очень мало. Я рекомендую обратиться к ресурсам, в сети, на которых собираются професиионалы... их можно легко найти обычным поиском. Мне лично нравится форум: http://www.planetaexcel.ru/forum.php P.S. Скажите пожалуста, как вы нашли эту страницу? Она вообще должна быть исключена из обхода поисковых машин, поскольку этот форум служебный.

Bulat: Спасибо за совет и ссылки на качественные ресурсы! ХБЗ. По запросу "готовые формулы" ваша страничка - первая в поиске. Правильно ли я понял, что больше сюда ходить нежелательно?

Тренер: Приходите играть в футбол к нам : )

Bulat: а где вы тренеруете? Какой город? Какого уровня команду?

Тренер: Bulat пишет: а где вы тренеруете? Какой город? Какого уровня команду? По прежнему тренерую команду мирового уровня: на Звёздной, в Санкт-Петербурге... это в России... планета Земля : )

les: если в таблице есть отрицательные числа (долги) и положительные (аванс). Как в отдельную ячейку суммировать отрицательные и в отдельную положительные числа

Тренер: Если нет желания учить работу с массивами, используйте: СУММЕСЛИ

Виктор: Доброго времени суток Уважаемые! Подскажите мне, пожалуйста, как решить следующую задачу: имеется файл .xls, в котором на трех листах представлены определенные данные, необходимо на VBA написать цикл, в котором указать из каких ячеек расположенных в одну строку (строк достаточно много и информация в них будет обновляться периодически), выбирать данные, копировать их на новый лист этого файла, т.е. когда в ячейке одной строки заканчиваются данные и последующая ячейка пуста, переходить на новую строку и запускать данный цикл заново до последних данных в файле?

Тренер: Уважаемый Виктор! Здесь Вы вряд ли встретите человека способного даже просто понять о чём идёт речь... ну разве, что меня : ) Но я не пишу вообще, с использованием макросов, надстроек, интераций, новых функций из последних версий Excel... так как ставлю задачу, чтоб документ работал у ЛЮБОГО его открывшего, с любой версией Офиса и с настройками "по умолчанию". Посему, для решения сложных задач - крайне рекомендую форум: http://www.planetaexcel.ru/forum.php Где, кстати, я был признан лучшим програМистом, из всех тренеров по футболу : ) Там Вы встретите много доброжелательных людей, способных помочь... но не выполнить за Вас Вашу работу.

Виктор: Спасибо за информацию Тренер! :)

toxic: Тренер пишет: P.S. Скажите пожалуста, как вы нашли эту страницу? Она вообще должна быть исключена из обхода поисковых машин, поскольку этот форум служебный. Хммм... Из поискового запроса по словам "Формулы эксель" на Яндекс: EXCEL- Готовые формулы Форум » КОМПЬЮТЕР » EXCEL- Готовые формулы » Ответить. … Это можно сделать с помощью формул Excel, которые Вы найдете ниже. wap.moitrener.forum24.ru › … копия ещё 3я позиция, поздравляю! Ещё немного и будете лидером... :)

Тренер: Да... смешно как инфаркт.

toxic: Дело в том, что форумы "forum24.ru" являются частью большого сервиса бесплатных форумов, которые взаимораскручиваются за счет привязки к одному доменному имени. Достаточно прописать в поисковике сам домен и его поддомены начнут индексироваться как его неотъемлемая часть. Это выгодно для новичков, но, как оказалось, лишнее для Вас. Если бы Вы были полноправным хозяином форума, а не пользователем скрипта, то смогли бы запретить индексацию за счет всего одного лишь файла .htaccess В сложившейся же ситуации Вам стоит обратиться к администрации сервиса с просьбой. Вы можете иметь такой-же форум на бесплатном хостинге и быть его полным хозяином на том же сервисе ayola.net udodovich[dog]yandex.ru

Тренер: Спасибо за информацию. Этот форум несколько лет не находился Яндексом вообще... и вот такое началось : )

toxic: Не за что. Исправлю ошибку. Файл отвечающий за индексацию, а равно за просматриваемые поисковыми роботами страницы(каталоги) сайта называется "robots.txt" Извините за грубейшую ошибку.

Ангелиель: Тренер, я вижу вы помогаете со всякого рода сложностями с экселем у меня возникла проблема, я просто не знаю всех формул, их назначения и возможностей применения так вот, делаю прайс с выпадающими списками, по ним формируется сумма с помощью формулы индекс но есть одна позиция в прайсе, которая зависит от выбора предыдущей позиции, то есть есть одна таблица: оборудование к\10 ю\30 где \ обозначает разделение столбцов и вторая таблица: скидка от объема объем \ к \ ю 5-10 \ 1,2 \ 1,3 10-20 \ 1 \ 1,1 20-50 \ 0,9 \ 0,95 вот я и не знаю, что прописывать в формуле

Тренер: Я тоже не знаю всех формул, но этого и не требуется для решения столь не сложных задач : ) Попробуйте изучить функцию ДВССЫЛ, описание есть здесь: http://www.planetaexcel.ru/tip.php?aid=64

Ангелиель: и правда все оказалось так просто)

borozda: Тренер Здравствуйте уважаемый тренер. Я хотел у Вас спросить. У меня есть цифры в столбцах 2 5 10 33 4 4 23 2 5 2 2 4 9 7 4 19 5 8 41 55 так вот в столбцах цифры 2 и 4 есть в каждом столбце. Мне нужна формула которая будет считать "связки" цифр по две или три или даже 4 штуки. Такое возможно в Екселе? Кирилл

Тренер: Вы очень широко ставите вопрос, нужна конкретная задача... под неё и будет конкретная формула. А так не ясно сколько таких связок возможно, а возможно и совпадений... или последовательностей... Поэтому нужна задача из жизни или файл с примером. А вообще СЧЁТЕСЛИ наверное...

Маргарита: Добрый вечер. Не могу понять как мне в XL определить новую цену на товар имея старый объем выпуска 200ед, новый объем выпуска 207ед, старую цену 3835 и коэф, эластичности 1,9. спасибо

Тренер: Маргарита пишет: Добрый вечер. Не могу понять как мне в XL определить новую цену на товар имея старый объем выпуска 200ед, новый объем выпуска 207ед, старую цену 3835 и коэф, эластичности 1,9. спасибо Тут вполне можно обойтись калькулятором : )

Маргарита: Да в полне с Вами согласна,ЧТО И БЫЛО РАНЬШЕ СДЕЛАНО. Но все это надо отобразить в XL. сначало дисперсия. потом распределения Стьюдента с верхним и нижжним пределом.



полная версия страницы