Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Преобразование из столбца в сводную таблицу - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование из столбца в сводную таблицу (Формулы/Formulas)
Преобразование из столбца в сводную таблицу
zatmenie13 Дата: Воскресенье, 13.11.2022, 22:50 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Всем привет!
Столкнулся с трудной задачей. Имеются суточные данные по осадкам за более чем 60 лет и имеют вид одной длинной колбасы на 20 с лишнем тысяч строк. Мне необходимо собрать эти данные с таблицу, где будут отображаться сумма месячных осадков. Сначала начал вручную через формулу =сумм() указывать диапазоны, но сделав один год понял что это не вариант и пустая трата времени. Может мне кто ни будь сможет подсказать гениальную супер запутанную формулу как для каждой ячейки в финальной таблице подсчитать сумму соответствующего месяца и года?
Скорее всего там активно надо использовать формулы =смещ() и =поискпоз(), но у меня не хватило фантазии как это все завернуть.

К сожалению сам файл с данными прикрепить не могу, т.к. данные секретные, но набросал пример того что мне надо для лучшего понимания
К сообщению приложен файл: 9121423.xlsx(8.9 Kb)
 
Ответить
СообщениеВсем привет!
Столкнулся с трудной задачей. Имеются суточные данные по осадкам за более чем 60 лет и имеют вид одной длинной колбасы на 20 с лишнем тысяч строк. Мне необходимо собрать эти данные с таблицу, где будут отображаться сумма месячных осадков. Сначала начал вручную через формулу =сумм() указывать диапазоны, но сделав один год понял что это не вариант и пустая трата времени. Может мне кто ни будь сможет подсказать гениальную супер запутанную формулу как для каждой ячейки в финальной таблице подсчитать сумму соответствующего месяца и года?
Скорее всего там активно надо использовать формулы =смещ() и =поискпоз(), но у меня не хватило фантазии как это все завернуть.

К сожалению сам файл с данными прикрепить не могу, т.к. данные секретные, но набросал пример того что мне надо для лучшего понимания

Автор - zatmenie13
Дата добавления - 13.11.2022 в 22:50
Gustav Дата: Воскресенье, 13.11.2022, 23:04 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Формула для ячейки H7:
Код
=СУММЕСЛИМН($D$4:$D$1000; $A$4:$A$1000; $G7; $B$4:$B$1000; СТОЛБЕЦ()-СТОЛБЕЦ($G$6))


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеФормула для ячейки H7:
Код
=СУММЕСЛИМН($D$4:$D$1000; $A$4:$A$1000; $G7; $B$4:$B$1000; СТОЛБЕЦ()-СТОЛБЕЦ($G$6))

Автор - Gustav
Дата добавления - 13.11.2022 в 23:04
zatmenie13 Дата: Воскресенье, 13.11.2022, 23:40 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Формула для ячейки H7:


У меня просто нет слов.....это какая то магия! Все сработало за 3 секунды))) Спасибо большое)))
 
Ответить
Сообщение
Формула для ячейки H7:


У меня просто нет слов.....это какая то магия! Все сработало за 3 секунды))) Спасибо большое)))

Автор - zatmenie13
Дата добавления - 13.11.2022 в 23:40
cmivadwot Дата: Воскресенье, 13.11.2022, 23:42 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 5 ±
Замечаний: 0% ±

zatmenie13, а используя сводные таблицы не вариант? и что такое I II III
К сообщению приложен файл: 3480505.xlsx(23.2 Kb)
 
Ответить
Сообщениеzatmenie13, а используя сводные таблицы не вариант? и что такое I II III

Автор - cmivadwot
Дата добавления - 13.11.2022 в 23:42
KayRodus Дата: Понедельник, 14.11.2022, 02:34 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Gustav, Эм... Я вообще если честно не понял, что эта формула делает... У меня ничего не работает.
 
Ответить
СообщениеGustav, Эм... Я вообще если честно не понял, что эта формула делает... У меня ничего не работает.

Автор - KayRodus
Дата добавления - 14.11.2022 в 02:34
zatmenie13 Дата: Понедельник, 14.11.2022, 06:31 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

а используя сводные таблицы не вариант?


К сожалению я не профи и не знаю как это работает)

и что такое I II III


это месяцы

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

Эм... Я вообще если честно не понял, что эта формула делает... У меня ничего не работает.


я тоже ничего не понял, но у меня все заработало, результат проверил
 
Ответить
Сообщение
а используя сводные таблицы не вариант?


К сожалению я не профи и не знаю как это работает)

и что такое I II III


это месяцы

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

Эм... Я вообще если честно не понял, что эта формула делает... У меня ничего не работает.


я тоже ничего не понял, но у меня все заработало, результат проверил

Автор - zatmenie13
Дата добавления - 14.11.2022 в 06:31
jakim Дата: Понедельник, 14.11.2022, 09:09 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1123
Репутация: 297 ±
Замечаний: 0% ±

Excel 2010
Еще вариант с Power Query

[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Число"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Месяц", type text}}, "lt-LT"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Месяц", type text}}, "lt-LT")[Месяц]), "Месяц", "Данные", List.Sum)
in
    #"Pivoted Column"
[/vba]
К сообщению приложен файл: PT.xlsx(16.8 Kb)
 
Ответить
Сообщение
Еще вариант с Power Query

[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Число"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Месяц", type text}}, "lt-LT"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Месяц", type text}}, "lt-LT")[Месяц]), "Месяц", "Данные", List.Sum)
in
    #"Pivoted Column"
[/vba]

Автор - jakim
Дата добавления - 14.11.2022 в 09:09
Gustav Дата: Понедельник, 14.11.2022, 11:25 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
У меня ничего не работает
Это не разговор. Версию Excel, локаль, файл с "неработающей" формулой, скриншот, тип ошибки - в студию!


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
У меня ничего не работает
Это не разговор. Версию Excel, локаль, файл с "неработающей" формулой, скриншот, тип ошибки - в студию!

Автор - Gustav
Дата добавления - 14.11.2022 в 11:25
KayRodus Дата: Понедельник, 14.11.2022, 12:02 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Gustav, А вы текст моего обращения точно читали? Я же написал, что это гугл таблица. И скриншоты приложил с подробным описанием того, что мне надо. Если хотите, дайте свою гугл почту, я открою вам доступ к документу и вы увидите все сами. Я просто вообще не понял вашу формулу, она ни как не соотнеслась с тем, что у меня.
 
Ответить
СообщениеGustav, А вы текст моего обращения точно читали? Я же написал, что это гугл таблица. И скриншоты приложил с подробным описанием того, что мне надо. Если хотите, дайте свою гугл почту, я открою вам доступ к документу и вы увидите все сами. Я просто вообще не понял вашу формулу, она ни как не соотнеслась с тем, что у меня.

Автор - KayRodus
Дата добавления - 14.11.2022 в 12:02
Gustav Дата: Понедельник, 14.11.2022, 12:56 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
KayRodus, Вы уверены, что пишите в ТУ ТЕМУ, в которой задавали свой вопрос? Здесь от Вас было только сообщение #5, в котором ни слова ни про Гугл таблицу, ни про доступ, ни про скриншоты.

P.S. А Ваша тема - вот она: Вывод информации в ячейку из СЕГОДНЯ и там пока тишина.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Понедельник, 14.11.2022, 14:25
 
Ответить
СообщениеKayRodus, Вы уверены, что пишите в ТУ ТЕМУ, в которой задавали свой вопрос? Здесь от Вас было только сообщение #5, в котором ни слова ни про Гугл таблицу, ни про доступ, ни про скриншоты.

P.S. А Ваша тема - вот она: Вывод информации в ячейку из СЕГОДНЯ и там пока тишина.

Автор - Gustav
Дата добавления - 14.11.2022 в 12:56
KayRodus Дата: Понедельник, 14.11.2022, 16:43 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Gustav, Точно... Прошу прощения. У сайта такой старый формат, что я запутался и влез не туда)).
 
Ответить
СообщениеGustav, Точно... Прошу прощения. У сайта такой старый формат, что я запутался и влез не туда)).

Автор - KayRodus
Дата добавления - 14.11.2022 в 16:43
zatmenie13 Дата: Понедельник, 14.11.2022, 18:03 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Еще вариант с Power Query


Если предыдущую формулу я просто не понял, то тут мои полномочия все. Но спасибо за ответ)
 
Ответить
Сообщение
Еще вариант с Power Query


Если предыдущую формулу я просто не понял, то тут мои полномочия все. Но спасибо за ответ)

Автор - zatmenie13
Дата добавления - 14.11.2022 в 18:03
Gustav Дата: Понедельник, 14.11.2022, 22:14 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Если предыдущую формулу я просто не понял

=СУММЕСЛИМН($D$4:$D$1000; $A$4:$A$1000; $G7; $B$4:$B$1000; СТОЛБЕЦ()-СТОЛБЕЦ($G$6))

Да ну что ж тут непонятного? :) Не сталкивались еще с СУММЕСЛИМН, что ли ? Это функция суммирования по нескольким критериям. Введена с версии Excel 2010.

$D$4:$D$1000 - числа из этого диапазона суммируем, если выполняются следующие условия:
$A$4:$A$1000 = $G7 - это годы, т.е. колонка годов = значению года из текущей строки свода
$B$4:$B$1000 = СТОЛБЕЦ()-СТОЛБЕЦ($G$6) - это месяцы, т.е. колонка месяцев = значению месяца из 6-й (заголовочной) строки свода

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

[p.s.]Хотя неправ я, и, оказывается, вполне можно было обуздать Ваши "римские традиции": :) [/p.s.]
Код
=СУММЕСЛИМН($D$4:$D$1000; $A$4:$A$1000; $G7; $B$4:$B$1000; АРАБСКОЕ(H$6))


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Понедельник, 14.11.2022, 22:23
 
Ответить
Сообщение
Если предыдущую формулу я просто не понял

=СУММЕСЛИМН($D$4:$D$1000; $A$4:$A$1000; $G7; $B$4:$B$1000; СТОЛБЕЦ()-СТОЛБЕЦ($G$6))

Да ну что ж тут непонятного? :) Не сталкивались еще с СУММЕСЛИМН, что ли ? Это функция суммирования по нескольким критериям. Введена с версии Excel 2010.

$D$4:$D$1000 - числа из этого диапазона суммируем, если выполняются следующие условия:
$A$4:$A$1000 = $G7 - это годы, т.е. колонка годов = значению года из текущей строки свода
$B$4:$B$1000 = СТОЛБЕЦ()-СТОЛБЕЦ($G$6) - это месяцы, т.е. колонка месяцев = значению месяца из 6-й (заголовочной) строки свода

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

[p.s.]Хотя неправ я, и, оказывается, вполне можно было обуздать Ваши "римские традиции": :) [/p.s.]
Код
=СУММЕСЛИМН($D$4:$D$1000; $A$4:$A$1000; $G7; $B$4:$B$1000; АРАБСКОЕ(H$6))

Автор - Gustav
Дата добавления - 14.11.2022 в 22:14
cmivadwot Дата: Понедельник, 14.11.2022, 22:27 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 5 ±
Замечаний: 0% ±

zatmenie13, вот вариант со сводной таблицей....
К сообщению приложен файл: 4302799.xlsx(63.9 Kb)
 
Ответить
Сообщениеzatmenie13, вот вариант со сводной таблицей....

Автор - cmivadwot
Дата добавления - 14.11.2022 в 22:27
cmivadwot Дата: Понедельник, 14.11.2022, 22:39 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 85
Репутация: 5 ±
Замечаний: 0% ±

с 1.01.1950 по сегодня по вымышленным данным...
К сообщению приложен файл: 3629751.rar(445.8 Kb)
 
Ответить
Сообщениес 1.01.1950 по сегодня по вымышленным данным...

Автор - cmivadwot
Дата добавления - 14.11.2022 в 22:39
Serge_007 Дата: Вторник, 15.11.2022, 13:42 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 15538
Репутация: 2558 ±
Замечаний: ±

Excel 2016
У сайта такой старый формат
Что это значит?
Как у сайта может быть какой-либо формат?


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
У сайта такой старый формат
Что это значит?
Как у сайта может быть какой-либо формат?

Автор - Serge_007
Дата добавления - 15.11.2022 в 13:42
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование из столбца в сводную таблицу (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!