EXCEL- ГОТОВЫЕ И ПОЛЕЗНЫЕ ФОРМУЛЫ

Форум » КОМПЬЮТЕР » 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

Тренер: ЧИСЛО ПРОПИСЬЮ - ПО РУССКИ Лет примерно 10 назад, написал вот такой вот: Товарный чек Его до сих пор используют некоторые магазины, а значит всё работает... Сделано очень просто и даже наверно топорно, но начинающим писателям экселя, так думаю будет понятней. Все мысли на листе "Чек" - в диапазоне =A1:E28.

Тренер: АВТОМАТИЧЕСКАЯ СОРТИРОВКА ДАННЫХ ФОРУЛОЙ - БЕЗ МАКРОСОВ На примере наверно самой навороченной моей футбольной таблицы: http://moitrener.narod.ru/Progi/TabZ-1997.xls Всё интересное там на странице "Сорт" в основной формуле ранга по имени, она подписана. И ещё, всем кто учится работать с массивами - крайне советую посмотреть решение на странице "Начало" в диапазоне =BS3:CV32. Даже при таком, относительно небольшом, объёме данных, любой другой вариант поиска - жутко тормозил пересчёт листа.

Тренер: СУММИРОВАТЬ ПО ГОДУ РОЖДЕНИЯ Предположим, что на просмотр в спортшколу у меня записано 230 детей разного возраста, а мне надо узнать сколько из них 2006 года рождения: =СУММПРОИЗВ((1*(ГОД($D$1:$D$230)=2006))) В столбце "I" поставлена дата просмотра, а я хочу знать сколько детей 2006 года рождения уже просмотрено: {=СУММ(($I$1:$I$230<СЕГОДНЯ())*($B$1:$B$230=2006))}

Marina: Какую формулу(условие) ввести? Есть две ячейки: количество и вес. Необходимо, что бы при наличии данных в одной, а в другой = пусто ячейки выделялись цветом. Помогите, пожалуйста!

Тренер: Marina пишет: Какую формулу(условие) ввести? Есть две ячейки: количество и вес. Необходимо, что бы при наличии данных в одной, а в другой = пусто ячейки выделялись цветом. Помогите, пожалуйста! Решений много, напишу самое скучное... Выделяем ячейки A1 и B2 На панели инструментов, а она обычно сверху: Формат - Условное форматирование - выбираем формула (не значение) В окне пишем: =$A$1+$B$1=$A$1 жмём: Формат и там задаём цвет/заливку/шрифт/границы и т.д. Ок Логика формул условного форматирования несколько отличается от логики обычных формул Экселя и её надо просто понять. P.S. У меня Excel 2002

Marina: Спасибо. Только ячейки выделяются и в случае если обе пустые.. не подскажите, как этого избежать?

Тренер: Marina пишет: Спасибо. Только ячейки выделяются и в случае если обе пустые.. не подскажите, как этого избежать? Первую задачу я решил, а вторую и все последующие - решать Вам. Пройдите этот путь сами, он, как мне помнится, весьма интересный. При каждой остановке жмите F1 и читайте... читайте... читайте...

Тренер: Excel 2002 - общее количество знаков в ячейке не может превышать 1024. Если Вы написали формулу, которая больше данного значения, то ограничение можно обойти путём переименования имён листов, на которые ссылается формула, на более короткие. Вот например, я переименовал "Данные" в "Дан" и этим сократил количество знаков с 1049, до 979. =ЕСЛИ(Дан!$Y$8=0;СЦЕПИТЬ("с"&ТЕКСТ(E38;"Д")&ТЕКСТ(E38-1;"[$-419]МММ")&"="&ОКРВВЕРХ($H$3*(ВПР(C40;Дан!$W$4:$X$10;2;ЛОЖЬ));0,5);"кг/");ЕСЛИ(Дан!$Y$8=1;СЦЕПИТЬ("с"&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(1;Дан!32:32;0)+1);"Д"))&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(1;Дан!32:32;0)+1)-1;"[$-419]МММ")&"="&(ОКРВВЕРХ($H$3*ВПР(C40;Дан!W4:Y10;2;ЛОЖЬ)*1,1;0,5)&"кг/");ЕСЛИ(Дан!$Y$8=2;СЦЕПИТЬ("с"&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(2;Дан!32:32;0)+1);"Д"))&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(2;Дан!32:32;0)+1)-1;"[$-419]МММ")&"="&(ОКРВВЕРХ($H$3*ВПР(C40;Дан!W4:Y10;2;ЛОЖЬ)*1,2;0,5)&"кг/");ЕСЛИ(Дан!$Y$8=3;СЦЕПИТЬ("с"&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(3;Дан!32:32;0)+1);"Д"))&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(3;Дан!32:32;0)+1)-1;"[$-419]МММ")&"="&(ОКРВВЕРХ($H$3*ВПР(C40;Дан!W4:Y10;2;ЛОЖЬ)*1,3;0,5)&"кг/");СЦЕПИТЬ("с"&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(4;Дан!32:32;0)+1);"Д"))&ТЕКСТ(ИНДЕКС(Дан!31:31;0;ПОИСКПОЗ(4;Дан!32:32;0)+1)-1;"[$-419]МММ")&"="&(ОКРВВЕРХ($H$3*ВПР(C40;Дан!W4:Y10;2;ЛОЖЬ)*1,4;0,5)&"кг"))))) Формула подсчёта =ДЛСТР(W23), где в W23 формула без "=". Правда если переименовать обратно лист "Дан" в "Данные", формула порежется, а Excel 2002 - упадёт.

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