[마이크로소프트X오빠두엑셀] 업무 고민, 시원하게 해결! Copilot in Excel 기능 소개

df = xl("Table1[#모두]", headers=True)
 
#Forecasting #Passengers for the next 3 years using SARIMAX
from statsmodels.tsa.statespace.sarimax import SARIMAX
 
# Prepare the data
data = df.copy()
data.set_index('Month', inplace=True)
 
# Fit the SARIMAX model
model = SARIMAX(data['#Passengers'], order=(1, 1, 1), seasonal_order=(1, 1, 1, 12))
results = model.fit()
 
# Forecast for the next 3 years (36 months)
forecast = results.get_forecast(steps=36)
forecast_index = pd.date_range(start=data.index[-1], periods=37, freq='MS')[1:]
forecast_df = pd.DataFrame({'Forecast': forecast.predicted_mean, 'Lower CI': forecast.conf_int()['lower #Passengers'], 'Upper CI': forecast.conf_int()['upper #Passengers']}, index=forecast_index)
 
# Plot the results
plt.figure(figsize=(12, 6))
plt.plot(data.index, data['#Passengers'], label='Historical Data')
plt.plot(forecast_df.index, forecast_df['Forecast'], label='Forecast', color='red')
plt.fill_between(forecast_df.index, forecast_df['Lower CI'], forecast_df['Upper CI'], color='pink', alpha=0.3)
plt.xlabel('Month')
plt.ylabel('#Passengers')
plt.title('Passenger Forecast for the Next 3 Years')
plt.legend()
plt.show()